Update SQL Statement with subquery

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

Update SQL Statement with subquery

Author

Topic

huynhptk
Starting Member

2 Posts

Posted - 2009-10-19 : 10:21:53

Hi guys:

I'm trying to create an update SQL Statement that will allow me to mark 'SecDup' in a field within table Address where I can use that info to delete all records that have duplicate address. Here's how I have it:

The following showed me all records with duplicate addresses (only show 1 out of the duplicates). This statement produced 25 records, exactly what I'm looking for.

SELECT sec.AddressName, sec.Address1, sec.City, sec.State, sec.Zip, sec.AddressID, sec.cart
FROM Address prim
inner join Address sec on prim.AddressID <> sec.AddressID
where prim.Address1 = sec.Address1
and prim.City = sec.City
and prim.State = sec.State
and prim.Zip = sec.Zip
and prim.AddressName = sec.AddressName
and prim.AddressID > sec.AddressID
and prim.cart is null
order by prim.Address1

The following intended to help me mark the above records as 'SecDup'
However, this statement produced 0 records, nothing have been marked as 'SecDup'. What went wrong?

update address
set cart = 'secDup'
where cart in (SELECT sec.cart
FROM Address prim
inner join Address sec on prim.AddressID <> sec.AddressID
where prim.Address1 = sec.Address1
and prim.City = sec.City
and prim.State = sec.State
and prim.Zip = sec.Zip
and prim.AddressName = sec.AddressName
and prim.AddressID > sec.AddressID
and prim.cart is null)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-19 : 11:31:19

The third line of your UPDATE should probably be:


WHERE AddressID IN (SELECT sec.AddressID

If you post the version of SQL you are using, someone will probably be able to help you with a more efficient query.

huynhptk
Starting Member

2 Posts

Posted - 2009-10-19 : 16:20:07

Wonderful. That worked! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources