I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID = @.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID => @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used and
>> called from asp.net code). It it return a value greater than 0 than I
>> know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] => @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used
>> and called from asp.net code). It it return a value greater than 0 than
>> I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
>> same error when checking the syntax:
>>
>> CREATE PROCEDURE spDeleteCapitalApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =>> @.intApplicationID) +
>> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left
>> over. The goal is to have the SP return "0" upon successful deletions
>> (used and called from asp.net code). It it return a value greater than
>> 0 than I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the
>> word "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do
>> all his relationships "programmatically" thru code. My hands are tied
>> so I need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>>
>
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Thursday, March 29, 2012
Deleting SP syntax check please...
I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID =
@.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID =
> @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationI
D
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
> @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE Applicatio
nID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID =
@.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID =
> @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationI
D
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
> @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE Applicatio
nID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>
Deleting semi duplicates
Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example
identical except for an id field and a date-time-stamp field. For
example
Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10
I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with
Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10
Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.
JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:
DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)
--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)
Thanks. I'll give this a try.
J|||Remember that names are resoved to the nearer containing table
reference! You meant:
DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!
Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.
--
David Portas
----
Please reply only to the newsgroup
--sql
Deleting records.
Anyone have ideas on how to delete records (see code below). I keep on getting the error message:
"The column prefix 'employee' does not match with a table name or alias name used in the query."
All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?
delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.
Thanks again.sql
"The column prefix 'employee' does not match with a table name or alias name used in the query."
All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?
delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.
Thanks again.sql
Deleting records to save DISK Space...
Hi,
In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately? Should I run some DBCC command to get some disk space ?
if SO ..!! What should I do after deleting the records..??
Thanks
Cheriyan."Always be sure to backup your records ... before you delete them ... "|||Have a look in SQL Book online to this command
DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)|||Kim Tripp presented a good session at the PASS conference a couple weeks ago about removing data using filegroups. If you are a PASS member, you can download the presentation here:
http://ew.sqlpass.org/ew/pass/callpapers/attach/TRIPP-Rolling_Range_For_Print.zip
Anyone can also download her scripts here:
http://www.sqlskills.com/pastConferences.asp
In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately? Should I run some DBCC command to get some disk space ?
if SO ..!! What should I do after deleting the records..??
Thanks
Cheriyan."Always be sure to backup your records ... before you delete them ... "|||Have a look in SQL Book online to this command
DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)|||Kim Tripp presented a good session at the PASS conference a couple weeks ago about removing data using filegroups. If you are a PASS member, you can download the presentation here:
http://ew.sqlpass.org/ew/pass/callpapers/attach/TRIPP-Rolling_Range_For_Print.zip
Anyone can also download her scripts here:
http://www.sqlskills.com/pastConferences.asp
Deleting records that get too old
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?WHERE date_file > DATEADD(dd,90,GETDATE())|||"Stay away from the light!" [Donkey from "Shrek"]|||"Stay away from the light!" [Donkey from "Shrek"]
Hey, I thought it was my job to handle the disconnected ramblings....
And btw, you do have a row added datetime column, right?
Of course you do.
(hey buddy, wanna buy a watch...cheap)|||Disconneted ramblings...
...Brett's job. Not yours.
...or rdjabarovs...
...<insert random Maragarita reference here>...
[sniped!]
...or connect the ramblings with a join, but not in WHERE clause.
WHERE disconnected_ramblings = ...
Not good.
Is it Friday yet?|||I'm confused.
Huh?
I think I lost my =|||Kansas?
There are only 2 things that come from Kansas boy.....
Steers...ok never mind
BUT, can you tell me the movie?
Did we scare him/her off...
And what about DB2
Damn...gotta run to THE city...
later|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.|||WHERE date_file > DATEADD(dd,90,GETDATE())Wait a minute, shouldn't it be "WHERE date_file < DATEADD(dd,-90,GETDATE())"?|||Now WHERE did I put that backup...?|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.
They did...it's called REXX
I love the INTERPRET keyword most of all scarecrow...
Hey, I thought it was my job to handle the disconnected ramblings....
And btw, you do have a row added datetime column, right?
Of course you do.
(hey buddy, wanna buy a watch...cheap)|||Disconneted ramblings...
...Brett's job. Not yours.
...or rdjabarovs...
...<insert random Maragarita reference here>...
[sniped!]
...or connect the ramblings with a join, but not in WHERE clause.
WHERE disconnected_ramblings = ...
Not good.
Is it Friday yet?|||I'm confused.
Huh?
I think I lost my =|||Kansas?
There are only 2 things that come from Kansas boy.....
Steers...ok never mind
BUT, can you tell me the movie?
Did we scare him/her off...
And what about DB2
Damn...gotta run to THE city...
later|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.|||WHERE date_file > DATEADD(dd,90,GETDATE())Wait a minute, shouldn't it be "WHERE date_file < DATEADD(dd,-90,GETDATE())"?|||Now WHERE did I put that backup...?|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.
They did...it's called REXX
I love the INTERPRET keyword most of all scarecrow...
deleting records takes forever
hi there.
I've got a bit of a problem on a clustered sql server. I don't know
if the cluster has anything to do with it, this is an entirely new
configuration for our company. We've moved out of the days of using
"white-box" pcs for servers to two dl380s clustered with an msa 1000
via fibre.
the servers are win2000 ent
mssql 2000 ent sp 3 (iirc)
with the advent of all this great technology, the big-brains
determined an excellent way to recoup costs of such an expense is to
release our dba. nice.
anyway. I've never run into this so I'm posting to this group, musing
whether this is due to clustering, table indexes or something else.
The cluster appears to be set up fine and we (small-brains) even
decided it was preduent to invest the money in calling MS for support.
Here'e the heart of the issue...
When deleting records from tables with indexes (mostly), by the index,
it takes absolutely forever. It took 8.5 hours to delete 1300 rows.
These are simple indexes, one field each, two indexes per table.
I say mostly because of the following:
deleting by the index, long time
deleting not by the index, fine execution
some tables, not deleting by the index, long time
example, contract table has two indexes on enteredby and contractid
(there are other objects, sps, views, triggers, constraints, etc.)
delete * from contract where enteredby = 5 and contractid < 1000
extremely long time to execute
delete * from contract where enteredby = 5
and
delete from contract where contractid < 1000
extremely long time
delete from contract where program > 1
fine execution
in another table - reports - deleting anything from that table results
in a long execution time whether i'm using indexes or not.
Now, when I select into another table (iirc just copies the data) and
perform any of the above (even on the reports table) execution is
perfect. Deletes several hundred rows <1 second.
Can anyone give me some guidance here?
thanks
>delete * from contract where enteredby = 5 and contractid < 1000
yeah, I know i messed that up
> decided it was preduent to invest the money in calling MS for support.
that too if anyone else noticed
|||Thanks for the suggestions.
In the event this ever gets indexed, it turned out that there were
constraints on the Comments table that referenced the Contract table.
I've got a bit of a problem on a clustered sql server. I don't know
if the cluster has anything to do with it, this is an entirely new
configuration for our company. We've moved out of the days of using
"white-box" pcs for servers to two dl380s clustered with an msa 1000
via fibre.
the servers are win2000 ent
mssql 2000 ent sp 3 (iirc)
with the advent of all this great technology, the big-brains
determined an excellent way to recoup costs of such an expense is to
release our dba. nice.
anyway. I've never run into this so I'm posting to this group, musing
whether this is due to clustering, table indexes or something else.
The cluster appears to be set up fine and we (small-brains) even
decided it was preduent to invest the money in calling MS for support.
Here'e the heart of the issue...
When deleting records from tables with indexes (mostly), by the index,
it takes absolutely forever. It took 8.5 hours to delete 1300 rows.
These are simple indexes, one field each, two indexes per table.
I say mostly because of the following:
deleting by the index, long time
deleting not by the index, fine execution
some tables, not deleting by the index, long time
example, contract table has two indexes on enteredby and contractid
(there are other objects, sps, views, triggers, constraints, etc.)
delete * from contract where enteredby = 5 and contractid < 1000
extremely long time to execute
delete * from contract where enteredby = 5
and
delete from contract where contractid < 1000
extremely long time
delete from contract where program > 1
fine execution
in another table - reports - deleting anything from that table results
in a long execution time whether i'm using indexes or not.
Now, when I select into another table (iirc just copies the data) and
perform any of the above (even on the reports table) execution is
perfect. Deletes several hundred rows <1 second.
Can anyone give me some guidance here?
thanks
>delete * from contract where enteredby = 5 and contractid < 1000
yeah, I know i messed that up
> decided it was preduent to invest the money in calling MS for support.
that too if anyone else noticed
|||Thanks for the suggestions.
In the event this ever gets indexed, it turned out that there were
constraints on the Comments table that referenced the Contract table.
Deleting records returned by datareader
I have a function that opens a connection to an SQL database, issues aSELECT command, and reads the records with an OleDbDataReader. As therecords are read, any that match certain criteria are deleted with aDELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed toSQL Server, I get a database timeout error when attempting to do theDELETE. I suspect the reason is that the connection the reader has openhas the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property"Mode=Share Deny None". I wonder if there is some similar way to tellSQL Server to allow editing of records that are open for reading withan OleDbDataReader.
Any help would be appreciated.
Can anyone tell me how to set up an SQL Server connection so thatopening a connection for read by an OleDbDataReader doesn't lock therecords? I know that I could read all the records into a datagrid, andthen delete the required rows, or that I could add the records thatneed to be deleted to an arraylist for later deletion. But this seemsmuch more complicated than it should be.
All I really need to do is open a datareader, read through the recordsone at a time, and delete a few of the records that match certaincriteria. This was easily done with MS Access, but with SQL Server(using the coonnection string described in my original post) I get atimeout error when I try to delete a record. I am presuming that atimeout occurs because the record I am trying to delete is locked bythe datareader.
Someone surely has a simple answer to this problem.
|||Go to Enterprise Manager click on tools then Query Analyzer and click on tools again then Options then click on Connections there are a few options see if one could help. But I would also run the SQL statement timing out in the Query Analyzer and click on show plan before execution to see if a minor change in the statement will make a difference and use the Profiler to create a trace on statement start and statement close events. I would also run a search for Query Governor in the BOL(books online) see what adjustments you need. Hope this helps.
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed toSQL Server, I get a database timeout error when attempting to do theDELETE. I suspect the reason is that the connection the reader has openhas the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property"Mode=Share Deny None". I wonder if there is some similar way to tellSQL Server to allow editing of records that are open for reading withan OleDbDataReader.
Any help would be appreciated.
Hi,
If you are using SQL Server, use SqlClient class.
The SqlClient classes use the native SQL Server drivers to access a database, while the OleDb classes use the generic OLE-DB interface
HTH
|||Thanks for the tip, HTH, but the application was originally written touse MS Access databases using the OleDb classes, and then modificationswere made to upgrade to SQL Server. While using the native SQL driversmay be more efficient, I'm not looking to re-write the application atthe moment - I just want to resolve the issue at hand.
Can anyone tell me how to set up an SQL Server connection so thatopening a connection for read by an OleDbDataReader doesn't lock therecords? I know that I could read all the records into a datagrid, andthen delete the required rows, or that I could add the records thatneed to be deleted to an arraylist for later deletion. But this seemsmuch more complicated than it should be.
All I really need to do is open a datareader, read through the recordsone at a time, and delete a few of the records that match certaincriteria. This was easily done with MS Access, but with SQL Server(using the coonnection string described in my original post) I get atimeout error when I try to delete a record. I am presuming that atimeout occurs because the record I am trying to delete is locked bythe datareader.
Someone surely has a simple answer to this problem.
|||Go to Enterprise Manager click on tools then Query Analyzer and click on tools again then Options then click on Connections there are a few options see if one could help. But I would also run the SQL statement timing out in the Query Analyzer and click on show plan before execution to see if a minor change in the statement will make a difference and use the Profiler to create a trace on statement start and statement close events. I would also run a search for Query Governor in the BOL(books online) see what adjustments you need. Hope this helps.
deleting records older than 180 days
I have table with over 1 billion rows, which I'd like to purge by deleting
all records older than 180 days from the current date (using the getdate()
function). Any help here would be great. Thanks."Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.
If you have a datetime stamp in the table, then this is relatively simple.
DELETE <table name>
WHERE DATEDIFF( dd, <datetime column name> , GETDATE() ) > 180
If you don't have a datetime stamp in the table, then maybe one of your
related tables can give this information.
If you supply the DDL for your table and some sample data, we can help you
out with a better query.
See the following link for more information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA|||Well Rob, I'm assuming that you have a date field that you can use to
reference?
Assuming you have, the process is quiet simple, but depending on how many
rows you are trying to delete in one go, you may have problems with your
log files.
The syntax would be something like,
delete from table where YOURdatetimefield < (dd, -180, getdate())
Like i say, you should keep an eye on your log space and also, the first
time you run this process could take a while.
Immy
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||excuse the missing sytax...
delete from table where YOURdatetimefield < dateadd(d, -180, getdate())
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uvH7FD8OGHA.3944@.tk2msftngp13.phx.gbl...
> Well Rob, I'm assuming that you have a date field that you can use to
> reference?
> Assuming you have, the process is quiet simple, but depending on how many
> rows you are trying to delete in one go, you may have problems with your
> log files.
> The syntax would be something like,
>
> Like i say, you should keep an eye on your log space and also, the first
> time you run this process could take a while.
> Immy
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>|||Hope you have a date field in your table...
In that case, try
select * from tablename where DATEDIFF(day,DATEfield,getdate())>=180
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Rob" wrote:
> I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||In addition to the other replies about how to implement the date comparison,
keep the following in mind:
For performance reasons, you will probably want to delete the rows in blocks
of 10,000 rather than one large transactions.
http://groups.google.com/group/micr...br />
22014423
Rather than purging the data from your database, you may want to migrate the
data to another table and then join it using a partitioned view.
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||>For performance reasons, you will probably want to delete the rows in blocks
>of 10,000 rather than one large transactions.
>http://groups.google.com/group/micr...201442
3
Good point, JT.
When I have been faced with this sort of massive purge of old data in
the past I found it easiest to process a single day at a time, in a
loop, oldest to newest. This keeps the rows per DELETE down to a very
conservative number. It also allowed me to add a pause in there
(WAITFOR DELAY) to let the system do other things, and the log purge
(this was using what is now called the simple recovery model, where
logs are truncated). This approach also let me interupt it and
restart it later if I had to, since it was written to start with the
oldest date currently in the file, and cancelling only wasted the
current "day" it was working on.
Roy Harvey
Beacon Falls, CT|||Thanks JT.
Through several responses to my post, I was able to construct and parse the
appropriate delete stmt. I found your suggestion of deleting in batches to b
e
recommendable. However, I'm struggling with putting a viable criteria for th
e
WHILE clause in order for the loop to start and NOT continue infinitely.
Thanks again.
"JT" wrote:
> In addition to the other replies about how to implement the date compariso
n,
> keep the following in mind:
> For performance reasons, you will probably want to delete the rows in bloc
ks
> of 10,000 rather than one large transactions.
> http://groups.google.com/group/micr... />
6922014423
> Rather than purging the data from your database, you may want to migrate t
he
> data to another table and then join it using a partitioned view.
> http://www.microsoft.com/technet/pr.../2005/spdw.mspx
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>
>|||Hi Rob,
In the example below, the WHERE clause would simply need to reference the
DateEntered column. The "set rowcount 1000" statement limits each iteration
to 1000 or fewer rows, and what prevents an infinite loop is the statement
"if @.@.rowcount = 0 break". When no more rows are available for deletion,
@.@.rowcount will be 0 and the loop will be terminated with the "break"
statement.
set rowcount 1000
while
delete from mytable where DateEntered < '2005/10/22'
if @.@.rowcount = 0 break
checkpoint
end
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:EE27EBFD-428E-465F-9EC1-CA5E03700458@.microsoft.com...
> Thanks JT.
> Through several responses to my post, I was able to construct and parse
> the
> appropriate delete stmt. I found your suggestion of deleting in batches to
> be
> recommendable. However, I'm struggling with putting a viable criteria for
> the
> WHILE clause in order for the loop to start and NOT continue infinitely.
> Thanks again.
> "JT" wrote:
>sql
all records older than 180 days from the current date (using the getdate()
function). Any help here would be great. Thanks."Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.
If you have a datetime stamp in the table, then this is relatively simple.
DELETE <table name>
WHERE DATEDIFF( dd, <datetime column name> , GETDATE() ) > 180
If you don't have a datetime stamp in the table, then maybe one of your
related tables can give this information.
If you supply the DDL for your table and some sample data, we can help you
out with a better query.
See the following link for more information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA|||Well Rob, I'm assuming that you have a date field that you can use to
reference?
Assuming you have, the process is quiet simple, but depending on how many
rows you are trying to delete in one go, you may have problems with your
log files.
The syntax would be something like,
delete from table where YOURdatetimefield < (dd, -180, getdate())
Like i say, you should keep an eye on your log space and also, the first
time you run this process could take a while.
Immy
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||excuse the missing sytax...
delete from table where YOURdatetimefield < dateadd(d, -180, getdate())
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uvH7FD8OGHA.3944@.tk2msftngp13.phx.gbl...
> Well Rob, I'm assuming that you have a date field that you can use to
> reference?
> Assuming you have, the process is quiet simple, but depending on how many
> rows you are trying to delete in one go, you may have problems with your
> log files.
> The syntax would be something like,
>
> Like i say, you should keep an eye on your log space and also, the first
> time you run this process could take a while.
> Immy
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>|||Hope you have a date field in your table...
In that case, try
select * from tablename where DATEDIFF(day,DATEfield,getdate())>=180
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Rob" wrote:
> I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||In addition to the other replies about how to implement the date comparison,
keep the following in mind:
For performance reasons, you will probably want to delete the rows in blocks
of 10,000 rather than one large transactions.
http://groups.google.com/group/micr...br />
22014423
Rather than purging the data from your database, you may want to migrate the
data to another table and then join it using a partitioned view.
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||>For performance reasons, you will probably want to delete the rows in blocks
>of 10,000 rather than one large transactions.
>http://groups.google.com/group/micr...201442
3
Good point, JT.
When I have been faced with this sort of massive purge of old data in
the past I found it easiest to process a single day at a time, in a
loop, oldest to newest. This keeps the rows per DELETE down to a very
conservative number. It also allowed me to add a pause in there
(WAITFOR DELAY) to let the system do other things, and the log purge
(this was using what is now called the simple recovery model, where
logs are truncated). This approach also let me interupt it and
restart it later if I had to, since it was written to start with the
oldest date currently in the file, and cancelling only wasted the
current "day" it was working on.
Roy Harvey
Beacon Falls, CT|||Thanks JT.
Through several responses to my post, I was able to construct and parse the
appropriate delete stmt. I found your suggestion of deleting in batches to b
e
recommendable. However, I'm struggling with putting a viable criteria for th
e
WHILE clause in order for the loop to start and NOT continue infinitely.
Thanks again.
"JT" wrote:
> In addition to the other replies about how to implement the date compariso
n,
> keep the following in mind:
> For performance reasons, you will probably want to delete the rows in bloc
ks
> of 10,000 rather than one large transactions.
> http://groups.google.com/group/micr... />
6922014423
> Rather than purging the data from your database, you may want to migrate t
he
> data to another table and then join it using a partitioned view.
> http://www.microsoft.com/technet/pr.../2005/spdw.mspx
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>
>|||Hi Rob,
In the example below, the WHERE clause would simply need to reference the
DateEntered column. The "set rowcount 1000" statement limits each iteration
to 1000 or fewer rows, and what prevents an infinite loop is the statement
"if @.@.rowcount = 0 break". When no more rows are available for deletion,
@.@.rowcount will be 0 and the loop will be terminated with the "break"
statement.
set rowcount 1000
while
delete from mytable where DateEntered < '2005/10/22'
if @.@.rowcount = 0 break
checkpoint
end
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:EE27EBFD-428E-465F-9EC1-CA5E03700458@.microsoft.com...
> Thanks JT.
> Through several responses to my post, I was able to construct and parse
> the
> appropriate delete stmt. I found your suggestion of deleting in batches to
> be
> recommendable. However, I'm struggling with putting a viable criteria for
> the
> WHILE clause in order for the loop to start and NOT continue infinitely.
> Thanks again.
> "JT" wrote:
>sql
Deleting records in the logfile
I have a database that is used to store a lot of data. We load the data on a
daily basis, several thousand records per day. The Log file is not needed,
so whats the best way to delete the records in it and reduce the size
daily basis, several thousand records per day. The Log file is not needed,
so whats the best way to delete the records in it and reduce the size
Thanks
Derrick"Derrick King" <derrick.king@.bradford.gov.uk> wrote in message
news:c1l7ag$ejl$1@.newsreaderm1.core.theplanet.net. ..
> I have a database that is used to store a lot of data. We load the data on
a
> daily basis, several thousand records per day. The Log file is not needed,
> so whats the best way to delete the records in it and reduce the size
> Thanks
> Derrick
You don't mention which version of MSSQL you have, but assuming it's 2000,
then see "Recovery Models" in Books Online. If you don't need transaction
log backups, the easiest solution is probably to set the database to Simple
recovery mode, which will automatically recover log space if possible.
If that's not acceptable, then you can consider transaction log backups (if
you don't already do that), which will truncate the log. Truncating the log
frees up log space but does not make it physically smaller, so you may also
need to use DBCC SHRINKFILE - see "Shrinking Databases".
Simon
deleting records in associated foeign key table
Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks
|||
There are a few options, but the best two, IMO, are:
Write a SQL statement that first deletes the associated records and then deletes the parent record. Imagine that you had a Categories table and a Products table, and there is a one to many relationship from Categories to Products. Now, imagine that we wanted to delete a category that has associated products. We'd first need to delete the Products - DELETE FROM Products WHERE CategoryID = @.CategoryIDToDelete - then we'd delete the category: DELETE FROM Categories WHERE CategoryID = @.CategoryIDToDelete.|||
Thank a lot Scott for quick response...i have got multiple tables and all these table are kind of chained up with each other using multiple foreign key .i am liking the idea of 'cascade delete',I have already set up the foreign key constraints in all these table.I will read up on cascade delete,,have no idea about it rightnow...
||| Scott is correct on the two main options. Given that this question is a "newbie" question, it's probably best to clarify that the first option is to write TWO sql statements, not one - a delete for each table.
David, do you mean,two sql statements(or as many delete as reqd for linked up tables) within one stored procedures or two(or multiple depends?) separate sql statements ?i am heavily using SP in my application.thanks
|||I mean 1 delete statement per table that needs deleting. Those delete statements can be in one stored procedure.
My comments were to avoid some one new to sql trying to issue a delete statement like this (because it will never work):
delete department and employee where department_id = 5
Deleting Records in a Table
Hi,
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!
Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query
select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)
If no, you should look up this post http://www.dbforums.com/t926686.html.
If there are more quetions, post again!
Greetings,
Carsten|||Originally posted by CarstenK
Hello!
Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query
select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)
If no, you should look up this post http://www.dbforums.com/t926686.html.
If there are more quetions, post again!
Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,
the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:
<synonym>.<column name>
So, just one table is used, but in two different forms.
Greetings,
Carsten|||Originally posted by CarstenK
Hi,
the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:
<synonym>.<column name>
So, just one table is used, but in two different forms.
Greetings,
Carsten
Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,
the normal syntax for delete looks like
delete from <table_name>
[where <where_condition>
Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.
Here is my preferred method, using joins instead of where clause:
delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1
WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.
blindman|||Hi, I used the owner moon:
create table moon.tempt
(tid integer primary key,
value integer)
insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)
delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)
finally:
select * from moon.tempt
tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run
select min(t2.tid) from moon.tempt t2 where t2.value = t1.value
...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.
Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.
blindman
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!
Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query
select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)
If no, you should look up this post http://www.dbforums.com/t926686.html.
If there are more quetions, post again!
Greetings,
Carsten|||Originally posted by CarstenK
Hello!
Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query
select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)
If no, you should look up this post http://www.dbforums.com/t926686.html.
If there are more quetions, post again!
Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,
the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:
<synonym>.<column name>
So, just one table is used, but in two different forms.
Greetings,
Carsten|||Originally posted by CarstenK
Hi,
the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:
<synonym>.<column name>
So, just one table is used, but in two different forms.
Greetings,
Carsten
Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.
Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,
the normal syntax for delete looks like
delete from <table_name>
[where <where_condition>
Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.
Here is my preferred method, using joins instead of where clause:
delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1
WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.
blindman|||Hi, I used the owner moon:
create table moon.tempt
(tid integer primary key,
value integer)
insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)
delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)
finally:
select * from moon.tempt
tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run
select min(t2.tid) from moon.tempt t2 where t2.value = t1.value
...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.
Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.
blindman
Deleting records from two tables
im having a very blond day .. carnt get my head round this today
Im rining SQl2005
i have two tables ( A & B ) A contains the 'master' record abd B contains
the 'detail' For every record in A there will be a minimum of 1 record in B
to a max of 1000000
Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
Table A also has a field 'Status'
What i want to do is create a stored procedure that deletes all records in
the Master (A) and Detail(B) tables when A.Status = 'T'
like i said this morning my minds a blanksomething like this?
begin tran
delete from B
from details B
where exists (select 1 from master_tbl A
where a.LedgerRef = b.LedgerRef
and a.status = 't')
delete from master_tbl
where status = 't'
commit tran
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE TABLE A
(
id int,
status char,
LedgerRef int
)
GO
CREATE TABLE B
(
LedgerRef int,
somedata varchar(100)
)
GO
INSERT A VALUES(1, 'T', 10)
INSERT A VALUES(2, 'F', 20)
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(20, 'don''t delete it')
DELETE B
FROM B JOIN A ON B.LedgerRef = A.LedgerRef
WHERE A.Status = 'T'
DELETE A
WHERE Status = 'T'
Greetings,
Urs
"Peter Newman" wrote:
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B contain
s
> the 'detail' For every record in A there will be a minimum of 1 record in
B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>|||Use CASCADE DELETE on TableB and you will only have to manage TableA.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:08FA6CBE-88EC-4775-80A1-6413FE99A16A@.microsoft.com...
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B
> contains
> the 'detail' For every record in A there will be a minimum of 1 record in
> B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>
Im rining SQl2005
i have two tables ( A & B ) A contains the 'master' record abd B contains
the 'detail' For every record in A there will be a minimum of 1 record in B
to a max of 1000000
Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
Table A also has a field 'Status'
What i want to do is create a stored procedure that deletes all records in
the Master (A) and Detail(B) tables when A.Status = 'T'
like i said this morning my minds a blanksomething like this?
begin tran
delete from B
from details B
where exists (select 1 from master_tbl A
where a.LedgerRef = b.LedgerRef
and a.status = 't')
delete from master_tbl
where status = 't'
commit tran
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE TABLE A
(
id int,
status char,
LedgerRef int
)
GO
CREATE TABLE B
(
LedgerRef int,
somedata varchar(100)
)
GO
INSERT A VALUES(1, 'T', 10)
INSERT A VALUES(2, 'F', 20)
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(20, 'don''t delete it')
DELETE B
FROM B JOIN A ON B.LedgerRef = A.LedgerRef
WHERE A.Status = 'T'
DELETE A
WHERE Status = 'T'
Greetings,
Urs
"Peter Newman" wrote:
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B contain
s
> the 'detail' For every record in A there will be a minimum of 1 record in
B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>|||Use CASCADE DELETE on TableB and you will only have to manage TableA.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:08FA6CBE-88EC-4775-80A1-6413FE99A16A@.microsoft.com...
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B
> contains
> the 'detail' For every record in A there will be a minimum of 1 record in
> B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>
Deleting Records From Sql Table
I AM TRYING TO DELETE RECORDS FROM THE SQL SERVER TABLE,
I AM USING THE COMMAND BELOW TO DELETE THOSE 71 RECORDS BUT MY WHOLE TABLE GOT DELETED AND HAD TO RECOVER FROM BACKUP..
SO WHAT IS WRONG WITH THE SYNTAX GIVEN BELOW
DELETE FROM dbo.Payment_Placement_AIMS
WHERE EXISTS
(select * from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First
employment'));since you are using exists in the where clause it is returning true value and the condition is satisfied in result the whole table is deleted (no specific record is mentioned)
following query will help you
DELETE FROM dbo.Payment_Placement_AIMS
WHERE Payment_Placement_AIMS.JC_ID in
(select Payment_Placement_AIMS.JC_ID from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First
employment'))sql
Deleting records from multiple tables in SQL server
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName
Table 4: Scores--StudentID, AssignmentID, Score
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@.classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
WHen you create a foreign key, there are some additional options you have to set to tell it to do the cascade delete.
If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:
Code Snippet
ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)
ON DELETE CASCADE;
If using SSMS, modify the table and go to where you created the relationship. If you look around in there you should see an option to set the "Delete Rule" Set that to CASCADE.
OK, the concept of deleting rows from multiple tables in a single delete statement cannot be done in just that statement. There is the concept of triggers on the tables that do deletes in a cascading style, but I would not recommend you do it that way for sake of control of the actions of the data.
But I udnerstand what you want to do, and the best way to explain it is this:
Say you have these tables and each one has a relationship up the chain. If you have Classes, and they are on Rosters, and Assignments are given to Classes and Scores have Students and Assignments you have to see which is the last one in the chain.
So in your case you have Classes that is the base and then you have Classes in Rosters (this is the next level) and you have Classes in Assignments (same level as Rosters).
So you have a relationship like
Classes - ClassID
|__Roster - ClassId
|__Assignments - ClassId
|Scores - AssignmentId
So say you no longer had ClassId 3 and you wanted to just get rid of all the records that are associated with ClassId 3. Here are the steps that you would need to take. You would delete in the reverse order than you inserted.
So in this case, you would want to use the ClassId = 3 to get all the assignments that have that ClassId and delete the Scores that have the AssignmentId and then delete the Assignments with the ClassId = 3
Then you would delete the Rosters with the ClassId = 3 and then finally delete the Classes with ClassId = 3
SQL:
DELETE Scores
FROM Assignments A
INNER JOIN Scores S ON A.AssignmentId = S.AssignmentId
WHERE A.ClassId = 3
DELETE Assignments
WHERE ClassId = 3
DELETE Roster
WHERE ClassId = 3
DELETE Classes
WHERE ClassId = 3
So you really just need to delete the Foreign Key tables records with the Primary Key record in it first and then delete the Primary Key records in the Primary table or Base table last.
HTH.
Ben Miller
|||I disagree with you an the answer that this cannot be done within one statement as the suggestions from Andrew about Cascading deletes should solve the problem, if the architecture is appropiate for the original poster.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Ben
I also disagree with your presentation. With a properly established set of relationships, CASCADE DELETE works wonderfully. The PK-FK relationships must be properly set-up, and there cannot be any circular relationships.
So you have a relationship like
Classes - ClassID
|__Roster - ClassId
|__Assignments - ClassId
|Scores - AssignmentId
In this situation, a deletion on [Classes] will remove related data from all lower tables. Deleting [Assignments] will also delete related data from [Scores]. A deletion on [Roster] or [Scores] will only affect those tables.
Deleting records from merge replication
I have a merge replication set up.
I need to delete some records at publisher which i don't want to be
replicated.
What should i do?
Thank you.
I suppose you could temporarily disable the merge triggers, but I really
wouldn't recommend this. If changes are made meanwhile, or if changes are
made to the subscriber rows you're keeping, there will be failures later on.
What I'd do is archive off these rows on the subscriber then do the delete.
You can amalgamate the rows (union) for the client application if necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have the same issue. I want to delete old date but hate to clog up
replication with 500K deletes. A user may have to synch for days to get thru
them.
Untested idea yet but I was considering pausing the merge agent job,
deleting the rows, isolating the deletes in msmerge_tombstone and genhistory
and deleting those specific rows. Restarting the agent would make the big
deletes get ignored , right?
Then run a similar script on the subscriber to trim the size too.
Anyone speculate on any unforseen ill effects? would it throw generation
out of whack?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"ravi lobo" wrote:
> I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||I can not disable DELETE trigger because as paul said i may some of the
deletes fired on the subscriber.
Tigermikefl,
I have tried your solution twice some time back. One time it worked. Failed
at the second time!
So i was forced to recreate the replication ! (It was very painful)
gen history table created some problem.
So i will not use your method unless somebody test it full-proff!
Also i need these functionality on a scheduled basis, stopping merge agent
followed by deleting tombstone will be painful.
|||You can change this behavior on the article level. What you need to do is
create an account in the PAL and limit the permissions on the publisher on
an as needed basis. Suppose the PAL account your particular subscriber is
pulling with is called Ravi. Grant select and update permissions to Ravi on
the table in the publisher - we'll call it raviTable.
Now right click on your publication, select publication properties, and
select the articles tab. Click on RaviTable, and click the browse button,
select Merging Changes, ensure that Delete is selected. Now what will
happen is that deletes will occur on the subscriber, and be replicated to
the publisher but be kicked back as conflicts. By default these deletes will
remain in the subscriber (IIRC) even with the compensate_For_errors setting
set to true (the default).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||Hilary,
Thank you for the reply.
I think the solution given by you prevents the DELETES at the subscriber
to replicate to the publisher.
I have a huge number of records which needs to be deleted. I am afraid
if I use your approach it will clog the n/w.
Infact I need to delete the records at the publisher.
Any suggestions?
"Hilary Cotter" wrote:
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi on
> the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes will
> remain in the subscriber (IIRC) even with the compensate_For_errors setting
> set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
>
|||The only suggestion I can make is to set ExchangeType to 1 (upload only),
but this will prevent all transactions from moving from the publisher to the
subscriber. You could drop the subscription, do the delete, and then
reinitialize, but I doubt this will work for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thank you for the reply.
> I think the solution given by you prevents the DELETES at the subscriber
> to replicate to the publisher.
> I have a huge number of records which needs to be deleted. I am afraid
> if I use your approach it will clog the n/w.
> Infact I need to delete the records at the publisher.
> Any suggestions?
> --
> "Hilary Cotter" wrote:
|||I can not use ExchangeType to 1,but can use dropping the subscription in the
green zone. Just wanted to avoid that.
Thank you Hilary, paul and Tigermikefl.
Good to know all those options.
"Hilary Cotter" wrote:
> The only suggestion I can make is to set ExchangeType to 1 (upload only),
> but this will prevent all transactions from moving from the publisher to the
> subscriber. You could drop the subscription, do the delete, and then
> reinitialize, but I doubt this will work for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...
>
>
|||Yes, that can be made to work and I have been through that process before.
No, I won't post scripts or the exact steps to follow. You are playing with
internal metadata the merge engine uses. The engine only moves what is in
the metadata tables, if it isn't there, it dosn't know about it. So, if you
remove the wrong row, you will forever be out of synch with the only option
being to reinitialize. They we'd have to hear about why merge isn't
working. If you understand the internal structures behind merge and exactly
how it moves data around the system, you already know how to do this and
don't need it explained. It is absolutely unsupported and very definitely
not recommended.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:A9500349-5C6B-4B61-919A-ADD05A4C3315@.microsoft.com...[vbcol=seagreen]
>I have the same issue. I want to delete old date but hate to clog up
> replication with 500K deletes. A user may have to synch for days to get
> thru
> them.
> Untested idea yet but I was considering pausing the merge agent job,
> deleting the rows, isolating the deletes in msmerge_tombstone and
> genhistory
> and deleting those specific rows. Restarting the agent would make the big
> deletes get ignored , right?
> Then run a similar script on the subscriber to trim the size too.
> Anyone speculate on any unforseen ill effects? would it throw generation
> out of whack?
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "ravi lobo" wrote:
|||And then you will have 100% of the deletes flow down to the subscriber as
well as 1 row for each row deleted flow back up to the publisher to be
logged into the conflict tables. You would then have to go into the
conflict table and clean out potentially thousands of entries. There is no
way I would recommend doing this.
If you need to do something like this, you need to find a maintenance window
when nothing else is being deleted. Then disable the delete trigger on the
publisher, issue your deletes, and reenable the trigger. The delete will
occur on the publisher, but since the trigger was disabled at that time,
there will be no delete logging for the merge engine to move. You will have
to remember to disable all of your validation scripts after this, because
the system will never validate successfully from that point forward.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fRI%239sFGHA.644@.TK2MSFTNGP09.phx.gbl...
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi
> on the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes
> will remain in the subscriber (IIRC) even with the compensate_For_errors
> setting set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
I need to delete some records at publisher which i don't want to be
replicated.
What should i do?
Thank you.
I suppose you could temporarily disable the merge triggers, but I really
wouldn't recommend this. If changes are made meanwhile, or if changes are
made to the subscriber rows you're keeping, there will be failures later on.
What I'd do is archive off these rows on the subscriber then do the delete.
You can amalgamate the rows (union) for the client application if necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have the same issue. I want to delete old date but hate to clog up
replication with 500K deletes. A user may have to synch for days to get thru
them.
Untested idea yet but I was considering pausing the merge agent job,
deleting the rows, isolating the deletes in msmerge_tombstone and genhistory
and deleting those specific rows. Restarting the agent would make the big
deletes get ignored , right?
Then run a similar script on the subscriber to trim the size too.
Anyone speculate on any unforseen ill effects? would it throw generation
out of whack?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"ravi lobo" wrote:
> I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||I can not disable DELETE trigger because as paul said i may some of the
deletes fired on the subscriber.
Tigermikefl,
I have tried your solution twice some time back. One time it worked. Failed
at the second time!
So i was forced to recreate the replication ! (It was very painful)
gen history table created some problem.
So i will not use your method unless somebody test it full-proff!
Also i need these functionality on a scheduled basis, stopping merge agent
followed by deleting tombstone will be painful.
|||You can change this behavior on the article level. What you need to do is
create an account in the PAL and limit the permissions on the publisher on
an as needed basis. Suppose the PAL account your particular subscriber is
pulling with is called Ravi. Grant select and update permissions to Ravi on
the table in the publisher - we'll call it raviTable.
Now right click on your publication, select publication properties, and
select the articles tab. Click on RaviTable, and click the browse button,
select Merging Changes, ensure that Delete is selected. Now what will
happen is that deletes will occur on the subscriber, and be replicated to
the publisher but be kicked back as conflicts. By default these deletes will
remain in the subscriber (IIRC) even with the compensate_For_errors setting
set to true (the default).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||Hilary,
Thank you for the reply.
I think the solution given by you prevents the DELETES at the subscriber
to replicate to the publisher.
I have a huge number of records which needs to be deleted. I am afraid
if I use your approach it will clog the n/w.
Infact I need to delete the records at the publisher.
Any suggestions?
"Hilary Cotter" wrote:
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi on
> the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes will
> remain in the subscriber (IIRC) even with the compensate_For_errors setting
> set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
>
|||The only suggestion I can make is to set ExchangeType to 1 (upload only),
but this will prevent all transactions from moving from the publisher to the
subscriber. You could drop the subscription, do the delete, and then
reinitialize, but I doubt this will work for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thank you for the reply.
> I think the solution given by you prevents the DELETES at the subscriber
> to replicate to the publisher.
> I have a huge number of records which needs to be deleted. I am afraid
> if I use your approach it will clog the n/w.
> Infact I need to delete the records at the publisher.
> Any suggestions?
> --
> "Hilary Cotter" wrote:
|||I can not use ExchangeType to 1,but can use dropping the subscription in the
green zone. Just wanted to avoid that.
Thank you Hilary, paul and Tigermikefl.
Good to know all those options.
"Hilary Cotter" wrote:
> The only suggestion I can make is to set ExchangeType to 1 (upload only),
> but this will prevent all transactions from moving from the publisher to the
> subscriber. You could drop the subscription, do the delete, and then
> reinitialize, but I doubt this will work for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...
>
>
|||Yes, that can be made to work and I have been through that process before.
No, I won't post scripts or the exact steps to follow. You are playing with
internal metadata the merge engine uses. The engine only moves what is in
the metadata tables, if it isn't there, it dosn't know about it. So, if you
remove the wrong row, you will forever be out of synch with the only option
being to reinitialize. They we'd have to hear about why merge isn't
working. If you understand the internal structures behind merge and exactly
how it moves data around the system, you already know how to do this and
don't need it explained. It is absolutely unsupported and very definitely
not recommended.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:A9500349-5C6B-4B61-919A-ADD05A4C3315@.microsoft.com...[vbcol=seagreen]
>I have the same issue. I want to delete old date but hate to clog up
> replication with 500K deletes. A user may have to synch for days to get
> thru
> them.
> Untested idea yet but I was considering pausing the merge agent job,
> deleting the rows, isolating the deletes in msmerge_tombstone and
> genhistory
> and deleting those specific rows. Restarting the agent would make the big
> deletes get ignored , right?
> Then run a similar script on the subscriber to trim the size too.
> Anyone speculate on any unforseen ill effects? would it throw generation
> out of whack?
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "ravi lobo" wrote:
|||And then you will have 100% of the deletes flow down to the subscriber as
well as 1 row for each row deleted flow back up to the publisher to be
logged into the conflict tables. You would then have to go into the
conflict table and clean out potentially thousands of entries. There is no
way I would recommend doing this.
If you need to do something like this, you need to find a maintenance window
when nothing else is being deleted. Then disable the delete trigger on the
publisher, issue your deletes, and reenable the trigger. The delete will
occur on the publisher, but since the trigger was disabled at that time,
there will be no delete logging for the merge engine to move. You will have
to remember to disable all of your validation scripts after this, because
the system will never validate successfully from that point forward.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fRI%239sFGHA.644@.TK2MSFTNGP09.phx.gbl...
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi
> on the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes
> will remain in the subscriber (IIRC) even with the compensate_For_errors
> setting set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
Deleting records from a table takes a long time
I have a table A that has about 35000 rows. Table B has about 2 million rows
.
Three columns colX,colY and colZ in table B have referential integrity
constraints with the primary key of table A. i.e. fk_1 for colX referencing
pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
referencing pk of table C ( There are other fks also on tableB and indexes)
When I delete rows from table A the delete takes a very long time sometimes
about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
there a way I can speed up the delete from table A? Or any other things I
should look into?Are your statistics up to date? See UPDATE STATISTICS in BOL.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Frank1213 wrote:
> I have a table A that has about 35000 rows. Table B has about 2
> million rows. Three columns colX,colY and colZ in table B have
> referential integrity constraints with the primary key of table A.
> i.e. fk_1 for colX referencing pk of table A, fk_2 for colY
> referencing pk of table B,fk_3 for colZ referencing pk of table C (
> There are other fks also on tableB and indexes) When I delete rows
> from table A the delete takes a very long time sometimes about 40
> minutes for about 10 rows( if I allow the delete sql to run) Is there
> a way I can speed up the delete from table A? Or any other things I
> should look into?
If table A has FK values in table B, then you can't delete from table A
unless you have set up cascading deletes. Have you? The other way to
delete is to manually remove the table B rows that match the PK in table
A, then delete from table A.
It's impossible to really guess where the holdup is in your testing. I
assume you have an index on the FK col1X in tableB, right?
David Gugick
Imceda Software
www.imceda.com|||Do you have an index on the foreign key in table B that references table A?
If not then when you delete from A it will probably be doing table scans of
table B to perform the referential integrity action (validate, cascade
delete/update).
"Frank1213" wrote:
> I have a table A that has about 35000 rows. Table B has about 2 million ro
ws.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX referenci
ng
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and indexes
)
> When I delete rows from table A the delete takes a very long time sometime
s
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Run the delete with showplan and statistics io to see where your slowdown is
at. Have you done this yet? Post the results, the original query, and the
ddl. Maybe we can help you out with a more educated guess. :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O10aVwQKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> Frank1213 wrote:
> If table A has FK values in table B, then you can't delete from table A
> unless you have set up cascading deletes. Have you? The other way to
> delete is to manually remove the table B rows that match the PK in table
> A, then delete from table A.
> It's impossible to really guess where the holdup is in your testing. I
> assume you have an index on the FK col1X in tableB, right?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Like Derrick, I would guess that putting an index on the foreign key in the
child table would help. Can you define "sometimes"? Does it sometimes run
in 40 ms? How busy is the system at the time? Do you have adequate
hardware?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?
.
Three columns colX,colY and colZ in table B have referential integrity
constraints with the primary key of table A. i.e. fk_1 for colX referencing
pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
referencing pk of table C ( There are other fks also on tableB and indexes)
When I delete rows from table A the delete takes a very long time sometimes
about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
there a way I can speed up the delete from table A? Or any other things I
should look into?Are your statistics up to date? See UPDATE STATISTICS in BOL.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Frank1213 wrote:
> I have a table A that has about 35000 rows. Table B has about 2
> million rows. Three columns colX,colY and colZ in table B have
> referential integrity constraints with the primary key of table A.
> i.e. fk_1 for colX referencing pk of table A, fk_2 for colY
> referencing pk of table B,fk_3 for colZ referencing pk of table C (
> There are other fks also on tableB and indexes) When I delete rows
> from table A the delete takes a very long time sometimes about 40
> minutes for about 10 rows( if I allow the delete sql to run) Is there
> a way I can speed up the delete from table A? Or any other things I
> should look into?
If table A has FK values in table B, then you can't delete from table A
unless you have set up cascading deletes. Have you? The other way to
delete is to manually remove the table B rows that match the PK in table
A, then delete from table A.
It's impossible to really guess where the holdup is in your testing. I
assume you have an index on the FK col1X in tableB, right?
David Gugick
Imceda Software
www.imceda.com|||Do you have an index on the foreign key in table B that references table A?
If not then when you delete from A it will probably be doing table scans of
table B to perform the referential integrity action (validate, cascade
delete/update).
"Frank1213" wrote:
> I have a table A that has about 35000 rows. Table B has about 2 million ro
ws.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX referenci
ng
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and indexes
)
> When I delete rows from table A the delete takes a very long time sometime
s
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Run the delete with showplan and statistics io to see where your slowdown is
at. Have you done this yet? Post the results, the original query, and the
ddl. Maybe we can help you out with a more educated guess. :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O10aVwQKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> Frank1213 wrote:
> If table A has FK values in table B, then you can't delete from table A
> unless you have set up cascading deletes. Have you? The other way to
> delete is to manually remove the table B rows that match the PK in table
> A, then delete from table A.
> It's impossible to really guess where the holdup is in your testing. I
> assume you have an index on the FK col1X in tableB, right?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Like Derrick, I would guess that putting an index on the foreign key in the
child table would help. Can you define "sometimes"? Does it sometimes run
in 40 ms? How busy is the system at the time? Do you have adequate
hardware?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?
Deleting Records From A Table
I AM TRYING TO DELETE RECORDS FROM THE TABLE.
Please check the 'Closing Brackets' in ' Where ' to your delete query you haven't mention any condition...
Tell me if that works|||
Please POST your exact ERROR for my reference. The syntax of the DELETE statement is WRONG. Check below syntax:
PLEASE CORRECT ME WITH THE SYNTAX
DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)
THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?
Quote:
Originally Posted by desirocks
I AM TRYING TO DELETE RECORDS FROM THE TABLE.
PLEASE CORRECT ME WITH THE SYNTAX
DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)
THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?
Please check the 'Closing Brackets' in ' Where ' to your delete query you haven't mention any condition...
Tell me if that works|||
Quote:
Originally Posted by desirocks
I AM TRYING TO DELETE RECORDS FROM THE TABLE.
PLEASE CORRECT ME WITH THE SYNTAX
DELETE From DPT_NEW_BINS WHERE (
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_Temp_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)
THE SELECT QUERY WORKS FOR ME BUT ONCE I ADD THE WHERE CLAUSE IT GIVES ME AN ERROR...CAN ANYBODY HELP ME OUT?
Please POST your exact ERROR for my reference. The syntax of the DELETE statement is WRONG. Check below syntax:
DELETE FROM <table_name> WHERE <column_name> IN (SELECT <column_name FROM <table_name>)
Deleting records from a parent table
How would I delete records from a parent table if it had no children? I know
this seems backwards but that is what I need to do.
Like....
delete from tblParentRecords if count of tblChildRecords = 0 or something
like this.
Thanks
Hi Craig,
Is it possible for you to provide DDL/sample record for your parent and
child tables?
Here is a small smaple for your reference:
create table p1
(
cp1 int primary key
)
GO
INSERT INTO p1 VALUES (1)
INSERT INTO p1 VALUES (2)
INSERT INTO p1 VALUES (3)
INSERT INTO p1 VALUES (4)
GO
create table p2
(
cc1 int foreign key references p1(cp1)
)
INSERT INTO p2 values (2)
INSERT INTO p2 values (3)
INSERT INTO p2 values (2)
select * from p1
where cp1 not in (select cc1 from p2)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Criag
Try the NOT exists
delete from tblparent P1 where NOT EXISTS(select * from tblChild C1 where
C1.Key = P1.Key)
kind regards
Greg O
"Craig" <NoSpam@.hotmail.com> wrote in message
news:OyKR0rJ7FHA.2816@.tk2msftngp13.phx.gbl...
> How would I delete records from a parent table if it had no children? I
> know this seems backwards but that is what I need to do.
> Like....
> delete from tblParentRecords if count of tblChildRecords = 0 or something
> like this.
>
> Thanks
>
>
|||I have many fields in the relationship.
The SQL below gets me the records I want to delete but I don't know how to
convert this to a delete statement.
tblBoilersTestDataBoilerWaterAveragesPerDay is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDate
FROM tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
How do I convert this to a delete statement or create a statement that
deletes parent record based on the number of child records = 0?
Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6RCZpoM7FHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hi Craig,
> Is it possible for you to provide DDL/sample record for your parent and
> child tables?
> Here is a small smaple for your reference:
> create table p1
> (
> cp1 int primary key
> )
> GO
> INSERT INTO p1 VALUES (1)
> INSERT INTO p1 VALUES (2)
> INSERT INTO p1 VALUES (3)
> INSERT INTO p1 VALUES (4)
> GO
> create table p2
> (
> cc1 int foreign key references p1(cp1)
> )
> INSERT INTO p2 values (2)
> INSERT INTO p2 values (3)
> INSERT INTO p2 values (2)
> select * from p1
> where cp1 not in (select cc1 from p2)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Craig,
I modified my sample for your reference
create table p1
(
cp1 int primary key,
cp2 int,
cp3 int
)
GO
INSERT INTO p1 VALUES (1,1,1)
INSERT INTO p1 VALUES (2,2,2)
INSERT INTO p1 VALUES (3,3,3)
INSERT INTO p1 VALUES (4,4,4)
GO
create table p2
(
cc1 int,
cc2 int,
cc3 int
)
INSERT INTO p2 values (2,2,2)
INSERT INTO p2 values (3,2,2)
INSERT INTO p2 values (4,4,4)
select * from p1, p2
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
select * from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and
p1.cp3 = p2.cc3
delete p1
from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 =
p2.cc3
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
You may perform the statement below, NOTE that please make it full backuped
and you have tested before
DELETE tblBoilersTestDataBoilerWaterAveragesPerDay
FROM
tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
this seems backwards but that is what I need to do.
Like....
delete from tblParentRecords if count of tblChildRecords = 0 or something
like this.
Thanks
Hi Craig,
Is it possible for you to provide DDL/sample record for your parent and
child tables?
Here is a small smaple for your reference:
create table p1
(
cp1 int primary key
)
GO
INSERT INTO p1 VALUES (1)
INSERT INTO p1 VALUES (2)
INSERT INTO p1 VALUES (3)
INSERT INTO p1 VALUES (4)
GO
create table p2
(
cc1 int foreign key references p1(cp1)
)
INSERT INTO p2 values (2)
INSERT INTO p2 values (3)
INSERT INTO p2 values (2)
select * from p1
where cp1 not in (select cc1 from p2)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Criag
Try the NOT exists
delete from tblparent P1 where NOT EXISTS(select * from tblChild C1 where
C1.Key = P1.Key)
kind regards
Greg O
"Craig" <NoSpam@.hotmail.com> wrote in message
news:OyKR0rJ7FHA.2816@.tk2msftngp13.phx.gbl...
> How would I delete records from a parent table if it had no children? I
> know this seems backwards but that is what I need to do.
> Like....
> delete from tblParentRecords if count of tblChildRecords = 0 or something
> like this.
>
> Thanks
>
>
|||I have many fields in the relationship.
The SQL below gets me the records I want to delete but I don't know how to
convert this to a delete statement.
tblBoilersTestDataBoilerWaterAveragesPerDay is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDate
FROM tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
How do I convert this to a delete statement or create a statement that
deletes parent record based on the number of child records = 0?
Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6RCZpoM7FHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hi Craig,
> Is it possible for you to provide DDL/sample record for your parent and
> child tables?
> Here is a small smaple for your reference:
> create table p1
> (
> cp1 int primary key
> )
> GO
> INSERT INTO p1 VALUES (1)
> INSERT INTO p1 VALUES (2)
> INSERT INTO p1 VALUES (3)
> INSERT INTO p1 VALUES (4)
> GO
> create table p2
> (
> cc1 int foreign key references p1(cp1)
> )
> INSERT INTO p2 values (2)
> INSERT INTO p2 values (3)
> INSERT INTO p2 values (2)
> select * from p1
> where cp1 not in (select cc1 from p2)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Craig,
I modified my sample for your reference
create table p1
(
cp1 int primary key,
cp2 int,
cp3 int
)
GO
INSERT INTO p1 VALUES (1,1,1)
INSERT INTO p1 VALUES (2,2,2)
INSERT INTO p1 VALUES (3,3,3)
INSERT INTO p1 VALUES (4,4,4)
GO
create table p2
(
cc1 int,
cc2 int,
cc3 int
)
INSERT INTO p2 values (2,2,2)
INSERT INTO p2 values (3,2,2)
INSERT INTO p2 values (4,4,4)
select * from p1, p2
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
select * from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and
p1.cp3 = p2.cc3
delete p1
from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 =
p2.cc3
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
You may perform the statement below, NOTE that please make it full backuped
and you have tested before
DELETE tblBoilersTestDataBoilerWaterAveragesPerDay
FROM
tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
Tuesday, March 27, 2012
Deleting records from a parent table
How would I delete records from a parent table if it had no children? I know
this seems backwards but that is what I need to do.
Like....
delete from tblParentRecords if count of tblChildRecords = 0 or something
like this.
ThanksHi Craig,
Is it possible for you to provide DDL/sample record for your parent and
child tables?
Here is a small smaple for your reference:
create table p1
(
cp1 int primary key
)
GO
INSERT INTO p1 VALUES (1)
INSERT INTO p1 VALUES (2)
INSERT INTO p1 VALUES (3)
INSERT INTO p1 VALUES (4)
GO
create table p2
(
cc1 int foreign key references p1(cp1)
)
INSERT INTO p2 values (2)
INSERT INTO p2 values (3)
INSERT INTO p2 values (2)
select * from p1
where cp1 not in (select cc1 from p2)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Criag
Try the NOT exists
delete from tblparent P1 where NOT EXISTS(select * from tblChild C1 where
C1.Key = P1.Key)
kind regards
Greg O
"Craig" <NoSpam@.hotmail.com> wrote in message
news:OyKR0rJ7FHA.2816@.tk2msftngp13.phx.gbl...
> How would I delete records from a parent table if it had no children? I
> know this seems backwards but that is what I need to do.
> Like....
> delete from tblParentRecords if count of tblChildRecords = 0 or something
> like this.
>
> Thanks
>
>|||I have many fields in the relationship.
The SQL below gets me the records I want to delete but I don't know how to
convert this to a delete statement.
tblBoilersTestDataBoilerWaterAveragesPer
Day is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDate
FROM tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
How do I convert this to a delete statement or create a statement that
deletes parent record based on the number of child records = 0?
Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6RCZpoM7FHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hi Craig,
> Is it possible for you to provide DDL/sample record for your parent and
> child tables?
> Here is a small smaple for your reference:
> create table p1
> (
> cp1 int primary key
> )
> GO
> INSERT INTO p1 VALUES (1)
> INSERT INTO p1 VALUES (2)
> INSERT INTO p1 VALUES (3)
> INSERT INTO p1 VALUES (4)
> GO
> create table p2
> (
> cc1 int foreign key references p1(cp1)
> )
> INSERT INTO p2 values (2)
> INSERT INTO p2 values (3)
> INSERT INTO p2 values (2)
> select * from p1
> where cp1 not in (select cc1 from p2)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Craig,
I modified my sample for your reference
create table p1
(
cp1 int primary key,
cp2 int,
cp3 int
)
GO
INSERT INTO p1 VALUES (1,1,1)
INSERT INTO p1 VALUES (2,2,2)
INSERT INTO p1 VALUES (3,3,3)
INSERT INTO p1 VALUES (4,4,4)
GO
create table p2
(
cc1 int,
cc2 int,
cc3 int
)
INSERT INTO p2 values (2,2,2)
INSERT INTO p2 values (3,2,2)
INSERT INTO p2 values (4,4,4)
select * from p1, p2
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
select * from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and
p1.cp3 = p2.cc3
delete p1
from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 =
p2.cc3
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
--
You may perform the statement below, NOTE that please make it full backuped
and you have tested before
DELETE tblBoilersTestDataBoilerWaterAveragesPer
Day
FROM
tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
this seems backwards but that is what I need to do.
Like....
delete from tblParentRecords if count of tblChildRecords = 0 or something
like this.
ThanksHi Craig,
Is it possible for you to provide DDL/sample record for your parent and
child tables?
Here is a small smaple for your reference:
create table p1
(
cp1 int primary key
)
GO
INSERT INTO p1 VALUES (1)
INSERT INTO p1 VALUES (2)
INSERT INTO p1 VALUES (3)
INSERT INTO p1 VALUES (4)
GO
create table p2
(
cc1 int foreign key references p1(cp1)
)
INSERT INTO p2 values (2)
INSERT INTO p2 values (3)
INSERT INTO p2 values (2)
select * from p1
where cp1 not in (select cc1 from p2)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Criag
Try the NOT exists
delete from tblparent P1 where NOT EXISTS(select * from tblChild C1 where
C1.Key = P1.Key)
kind regards
Greg O
"Craig" <NoSpam@.hotmail.com> wrote in message
news:OyKR0rJ7FHA.2816@.tk2msftngp13.phx.gbl...
> How would I delete records from a parent table if it had no children? I
> know this seems backwards but that is what I need to do.
> Like....
> delete from tblParentRecords if count of tblChildRecords = 0 or something
> like this.
>
> Thanks
>
>|||I have many fields in the relationship.
The SQL below gets me the records I want to delete but I don't know how to
convert this to a delete statement.
tblBoilersTestDataBoilerWaterAveragesPer
Day is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDate
FROM tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
How do I convert this to a delete statement or create a statement that
deletes parent record based on the number of child records = 0?
Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6RCZpoM7FHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hi Craig,
> Is it possible for you to provide DDL/sample record for your parent and
> child tables?
> Here is a small smaple for your reference:
> create table p1
> (
> cp1 int primary key
> )
> GO
> INSERT INTO p1 VALUES (1)
> INSERT INTO p1 VALUES (2)
> INSERT INTO p1 VALUES (3)
> INSERT INTO p1 VALUES (4)
> GO
> create table p2
> (
> cc1 int foreign key references p1(cp1)
> )
> INSERT INTO p2 values (2)
> INSERT INTO p2 values (3)
> INSERT INTO p2 values (2)
> select * from p1
> where cp1 not in (select cc1 from p2)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Craig,
I modified my sample for your reference
create table p1
(
cp1 int primary key,
cp2 int,
cp3 int
)
GO
INSERT INTO p1 VALUES (1,1,1)
INSERT INTO p1 VALUES (2,2,2)
INSERT INTO p1 VALUES (3,3,3)
INSERT INTO p1 VALUES (4,4,4)
GO
create table p2
(
cc1 int,
cc2 int,
cc3 int
)
INSERT INTO p2 values (2,2,2)
INSERT INTO p2 values (3,2,2)
INSERT INTO p2 values (4,4,4)
select * from p1, p2
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
select * from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and
p1.cp3 = p2.cc3
delete p1
from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 =
p2.cc3
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
--
You may perform the statement below, NOTE that please make it full backuped
and you have tested before
DELETE tblBoilersTestDataBoilerWaterAveragesPer
Day
FROM
tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)