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.
| Author |
Topic |
|
matticusfinch
Starting Member
9 Posts |
 Posted - 2005-07-14 : 12:23:26
|
| I have two tables in the same database, ARACCT and ARADDR2, that hold member information.The linking field between these two is the ARCODE field, in ARACCT this is a primary key, while in ARADDR2, it is not, due to the ability to put in multiple addresses per member.I need to delete duplicate addresses between these two tables, and have used this query to identify what these records are.select b.arcode, b.saddr1, b.saddr2, b.scity, b.sstate from aracct ainner join araddr2 bon a.arcode = b.arcodewhere a.arcode = b.arcode and a.addr1 = b.saddr1 and a.addr2 = b.saddr2 and a.city = b.scityand a.state = b.sstateThe problem is, I can't use a delete from araddr2 where arcodein(above query) to delete these records, as this would delete all of that arcode, regardless of the other fields.Any suggestions on how to do what I need?Matt |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-14 : 12:31:44
|
| What's the PK on araddr2 ?Kristen |
 |
|
|
matticusfinch
Starting Member
9 Posts |
Posted - 2005-07-14 : 13:48:56
|
| Well, there-in lies the kicker.There is none. The developers of the system were not terribly fond of doing things in a fundamentally sound way. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-14 : 14:06:25
|
Hehehe. OK.You say using a delete with "IN", would this do instead:DELETE BFROM aracct a INNER JOIN araddr2 b ON a.arcode = b.arcodeWHERE a.arcode = b.arcode AND a.addr1 = b.saddr1 AND a.addr2 = b.saddr2 AND a.city = b.scity AND a.state = b.sstate Kristen |
|
|
|
matticusfinch
Starting Member
9 Posts |
Posted - 2005-07-14 : 15:26:50
|
| Works great....thanks! |
|
|
|
|
|
|
|
|
Duplicate Records