Updating a recordset within a Self Join -- sql server data mining

Chris
2/3/2005 9:15:07 AM

Hi

I have a software table with a million records. I need to elimante all the
older version of IE that may be in the table.
This is the SQL I am using. The duplicate field is bit.

UPDATE software
SET Duplicate = 1
WHERE EXISTS (SELECT *
FROM Software AS sf2
WHERE sf2.ClientID = Software.ClientID
AND sf2.FileVersion < Software.Fileversion
AND Filename = 'iexplore.exe' and FileDescription = 'Internet
Explorer' )

This updates all 1,118,000+ records instead of the approx 4,000 it should.

Thanks in Advance

Adam Machanic
2/4/2005 11:58:38 AM

One possibility is that it's because you haven't prefixed the FileName and
FileDescription columns with the sf2 alias... If that doesn't work, please
post DDL + sample data.

UPDATE software
SET Duplicate = 1
WHERE EXISTS (SELECT *
FROM Software AS sf2
WHERE sf2.ClientID = Software.ClientID
AND sf2.FileVersion < Software.Fileversion
AND sf2.Filename = 'iexplore.exe' and sf2.FileDescription = 'Internet
Explorer' )

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]

sql server data mining : Updating a recordset within a Self Join