Identification of Rows to Delete

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

SQL Server 2000 Forums

SQL Server Development (2000)

Identification of Rows to Delete

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-16 : 17:03:26

I get regular updates (Excel Spreadsheets) from my customers. These updates contain about 10,000 to 30,000 rows containing lots of data plus "username" as the key.

To identify the rows in the database that should be tagged as deleted, I do the following:

update Users
Set
Deleted=1
from
users left outer join spreadsheet
on users.username=spreadsheet.username
where users.deleted=0 and spreadsheet.username is null

It seems like a "kludge" to me to use
spreadsheet.username is null

to detect rows that have no match.

I was wondering if that was status quo for SQL or if there might be a another way?

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 17:32:02

Not a kludge at all, it's a time-honored way to do it (the MS Access Unmatched Query Wizard uses this technique)

You could also use the following:

UPDATE U SET Deleted=1
FROM Users U
WHERE U.Deleted=0 AND
NOT EXISTS (SELECT * FROM Spreadsheet WHERE Username=U.UserName)

You may get slightly better performance, but in general the two are usually equivalent in performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources