where not exists with composite key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where not exists with composite key

Author

Topic

cykoko
Starting Member

3 Posts

Posted - 2011-12-21 : 07:53:52

Hi,

I am a complete newby, apologies in advance.

I have two tables with composite keys (Code, ID, DocNumber). The Invoice table is almost identical but has more rows in it than the Transactions table, and I want to mark those rows as “Delete”.

At the moment, no rows are being affected. I was wondering if someone could help with the logic, below is my code:

UPDATE Invoice
SET Status = 'Delete'
WHERE NOT EXISTS
(SELECT *
FROM Transactions
WHERE
Invoice.Code= Transactions.Code and
Invoice.ID = Transactions.ID and
Invoice.DocNumber = Transactions.DocNumber);

Many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 11:46:24

is there a unique valued column in Invoice which allows you to identify which ones to be deleted and which ones to be retained out of a duplicate group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cykoko
Starting Member

3 Posts

Posted - 2011-12-22 : 01:29:10

Hi Visakh,

No unfortunately I have to use Code, ID, DocNumber as a composite key in both tables and compare them. If all three match then I want to mark as deleted. ‘Not exists’ works well when I have a unique ID for other tables but I can’t get it to work by trying to compare all three (ie the bottom three rows of my code is where I am stuck).

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 01:40:27

quote:
Originally posted by cykoko

Hi Visakh,

No unfortunately I have to use Code, ID, DocNumber as a composite key in both tables and compare them. If all three match then I want to mark as deleted. ‘Not exists’ works well when I have a unique ID for other tables but I can’t get it to work by trying to compare all three (ie the bottom three rows of my code is where I am stuck).

Thanks for the help.

if thats case you will end up marking all records with same Code, ID, DocNumber combination as deleted. thats why i asked whether there's any differentiator which suggests which ones to keep and which ones to delete. If there's not already one, you can use logic like below which will retain a random one and remove others


UPDATE t
SET t.Status = 'Delete'
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Code, ID, DocNumber ORDER BY NEWID()) AS Rn,Status 
FROM tablename
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cykoko
Starting Member

3 Posts

Posted - 2011-12-22 : 02:40:12

Ok, now I get the logic, thanks so much for the help, I'll give it a try.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 03:59:35

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources