deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:

> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove an
y
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited b
ut
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>sql

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:
> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove any
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited but
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>

Deleting primary key when no foreign records exist?

Is there a trigger that would handle this situation? I have a 1 to
many between two tables and would like the primary key deleted if the
last foreign record is deleted.
Thanks in advance!Sure, in an AFTER trigger on the child table, something like (including a
scenario to try it out.):
set nocount on
go
create table parent
(
parentKey int primary key
)
create table child
(
childKey int primary key,
parentKey int foreign key references parent(parentKey)
)
insert into parent
select 1
union all
select 2
union all
select 3
insert into child
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
go
create trigger child$deleteTrigger
on child
after delete
as
--be sure to add error handling
delete parent
--this gets all parent rows that are related to the deleted child rows based
on the migrated key from the parent
where exists (select 1
from deleted
where parent.parentKey = deleted.parentKey)
--this excludes parents where a child still exists
and not exists (select 1
from child
join deleted
on child.parentKey =
deleted.parentKey
where parent.parentKey = deleted.parentKey)
go
delete child where parentkey = 1
select *
from parent
left outer join child
on parent.parentKey = child.parentKey
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1139625347.478136.146410@.g14g2000cwa.googlegroups.com...
> Is there a trigger that would handle this situation? I have a 1 to
> many between two tables and would like the primary key deleted if the
> last foreign record is deleted.
> Thanks in advance!
>|||Many thanks Louis!|||Yes, you can do this with a trigger, if you like to write procedural,
proprietary code. Look for a COUNT(*) = 0 in a PK-FK join.
Another way to do this is to put one "child" item the same table as the
"parent" since you seem to require at least one "child" as part of the
design. When you delete the "only child", you have to delete the
parent.
Oh, did I mention that the code is messy?|||> Another way to do this is to put one "child" item the same table as the "p
arent"
Joe,
Are you saying your solution conforms to 3NF?
What if later you'll need to delete the child row stored along with the
parent? You'll have to move another row from the child table to the
parent one. Are you claiming it's less messy than Louis's solution?
Also instead of selects against the child table you'll have to select
against a union of the child and the parent, right?
How would you enforse a unique constraint on the child?sql

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
Microsoft SQL-DMO
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.
OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.
|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
Microsoft SQL-DMO
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [
] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delet
e
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissio
ns
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissio
ns
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> it.
> Please help. Thanks.

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
--
Microsoft SQL-DMO
--
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.

Delete syntax to delete a record from one table if a matching value isn't found in another

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

kfrost:

Maybe something like:

DELETE FROM Broker_Rates
from Broker_Rates a
WHERE not exists
( select 0 from Broker b
where a.BID = B.id
)

Dave

|||

That appeared to do the trick. Curious, what does using the 0 in Select 0 person of your string above accomplish. I was using Select *.

Works, just curious for future reference.

Thanks.

|||

I use the zero because the select list is not relevant. Your syntax will work fine. I need to do something like "0 as dummy" so that it is more obvious that the item is a dummy item.

Dave

|||Cool. Thanks!!

Delete records that don exist in the destination

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

MShetty wrote:

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

That sounds like it will work. You basically need to compare the source and destination. Any records that are in the destination but not the source have to be removed - it sounds as if that is what you are attempting.

They can be deleted using an OLE DB Command. or you can push the records to be deleted into a temporary table and delete them using an Execute SQL Task.

-Jamie

|||

Hi Jamie,

Thx for the comments, I started working exactly the same way, but now am into an issue here. I am starting with two small tables as the first step. The table has this schema.

UserId (int) (PK)

UserName (varchar)

IsActive (bit) i am taking two DFT's one each to the two Databases and the selected records are passed onto a Merge Join. UserId is the join key here. Now the records that are having a match already are returned from this component right ? If I make the join type as a Ful Outer JOin and pass on to conditional split transform, how do I get the records that are in Destination DB but were deleted from the Source, I am just getting the newly added records and the updated one's. Please guide..

Thanks a lot

|||

I don't think you need a fullt outer join, just a left join will do it (with the incoming data on the left input).

Described here:

Get all from Table A that isn't in Table B
(http://www.sqlis.com/default.aspx?311)

-Jamie

|||

Thx for the inputs Jamie.

My problem of deleting the records in the destination that were removed from the Source DB was solved by using an IsNull (SrcTable.PK) in a conditional split and then direct those rows to an OLE DB Command Component.

delete record does not exist in table

I want to delete records in table1 if account number does not exist in table
2.
I have following code:
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
Should the above code work?
Any informaion is great appreciated,Yes, that should work just fine...
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2)
You can also use Not Exists
DELETE TABLE1 T1
WHERE Not Exists
(SELECT * FROM TABLE2
Where ACCOUNT_NUMBER =
T1.ACCOUNT_NUMBER)
"Souris" wrote:

> I want to delete records in table1 if account number does not exist in tab
le2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||To add to CBretana's response, your NOT IN construct will work as expected
unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
when there are one or more NULL values. Personally, I prefer NOT EXISTS
because that produces the behavior desired in most situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>I want to delete records in table1 if account number does not exist in
>table2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||Dan
I agree , but if you change 'a little bit :-)' his query it should work as
well as NOT EXISTS .
People just forget with NOT IN to add WHERE condition with an outer table.
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2 Where TABLE1.ACCOUNT_NUMBER =
TABLE2 .ACCOUNT_NUMBER)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OlRMglaRFHA.252@.TK2MSFTNGP12.phx.gbl...
> To add to CBretana's response, your NOT IN construct will work as expected
> unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
> when there are one or more NULL values. Personally, I prefer NOT EXISTS
> because that produces the behavior desired in most situations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>

Delete Query for records that do not exist in second table (Purging old records)

Hello, I have tried using the query below to delete records that exist in the SHIPPINGFORECAST table, but they do not exist in the neword01 table. That select query gives me the correct result set, but when I run it with the delete it removes all records from the SHIPPINGFORECAST table. Any help would be appreciated.

DELETE FROM SHIPPINGFORECAST
WHERE EXISTS
(SELECT dbo.SHIPPINGFORECAST.ORDERNUMBER, dbo.SHIPPINGFORECAST.JOBNAME, dbo.SHIPPINGFORECAST.SHIPDATE
FROM dbo.SHIPPINGFORECAST LEFT OUTER JOIN
dbo.neword01 ON dbo.SHIPPINGFORECAST.ORDERNUMBER = dbo.neword01.ordn07
WHERE (dbo.neword01.ordn07 IS NULL))

Hai,

As you have not mentioned which table is master and which is transaction we assumed @.ShippingForeCast as Master and @.NewOrd01 as transaction table and concluded the below query:

Please Clarify If'm wrong.

DECLARE @.ShippingForecast table(OrderNumber int, JobName varchar(50), ShipDate smalldatetime)

DECLARE @.NewOrd01 table(OrdN07 int)

DECLARE @.CurrentDate smalldatetime

SET @.CurrentDate = getdate()

-- Table - 1

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(1, 'A', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(2, 'B', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(3, 'C', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(4, 'D', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(5, 'E', @.CurrentDate)

-- Table - 2

INSERT INTO @.NewOrd01(OrdN07) VALUES(1)

INSERT INTO @.NewOrd01(OrdN07) VALUES(1)

INSERT INTO @.NewOrd01(OrdN07) VALUES(2)

INSERT INTO @.NewOrd01(OrdN07) VALUES(2)

INSERT INTO @.NewOrd01(OrdN07) VALUES(3)

INSERT INTO @.NewOrd01(OrdN07) VALUES(3)

-- Before Delete

SELECT * FROM @.ShippingForeCast

DELETE FROM @.ShippingForeCast

WHERE OrderNumber IN (

SELECT

S.OrderNumber

FROM @.ShippingForeCast AS S

LEFT JOIN @.NewOrd01 AS N

ON N.OrdN07 = S.OrderNumber

WHERE N.OrdN07 IS NULL )

-- ( OR )

--DELETE FROM @.ShippingForeCast WHERE OrderNumber NOT IN (SELECT OrdN07 FROM @.NewOrd01)

-- After Delete

SELECT * FROM @.ShippingForeCast

Regards,

Prashanthi.

|||

Worked like a charm! Thanks!