CODEWITHSUNDEEP
sqlsql-serverjoinsql-update
Sam P
Sam P

Reputation: 515

Update column from another table's column based on matching columns

The following query is updating all the (80k) records instead of the ones that match (5k). What is wrong with it and how can it be corrected?

update ALAM set ALAM.CDate = (select IDCP.CDate from IDCP 
                              where ALAM.ASID = IDCP.ASID and ALAM.AID = IDCP.AID 
                                    and ALAM.MCode = '10001')

Record Count of ALAM Table = 80,000 records approx Record Count of IDCP Table = 5,000 records approx

As additional information:

select ALAM.ASID, ALAM.AID, ALAM.CDate 
from ALAM, IDCP 
where ALAM.ASID = IDCP.ASID and ALAM.AID = IDCP.AID and ALAM.MCode = '10001'

result 5000 records approx

Upvotes: 4

Views: 12949

Answers (4)

Paul Morgan
Paul Morgan

Reputation: 32528

As tjekkles said you left off the WHERE clause on the update which you can pull out of the inner select:

update ALAM set ALAM.CDate = (select IDCP.CDate
                                from IDCP 
                               where ALAM.ASID = IDCP.ASID
                                 and ALAM.AID  = IDCP.AID ) 
 where ALAM.MCode = '10001'

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

MERGE INTO ALAM
   USING IDCP 
      ON ALAM.ASID = IDCP.ASID 
         AND ALAM.AID = IDCP.AID 
         AND ALAM.MCode = '10001'
WHEN MATCHED THEN
   UPDATE 
      SET CDate = IDCP.CDate;

Upvotes: 6

Tjekkles
Tjekkles

Reputation: 5612

In the first statement you haven't stated the WHERE clause on the main query. So it executes on all the records in ALAM (which I assume are 80K).

In the second query you join the 2 tables, so you only take the ones from ALAM that match the ones in IDCP. This is what went wrong.

UPDATE ALAM
SET a.CDate = i.CDate
FROM ALAM a INNER JOIN IDCP i ON a.ASID = i.ASID and a.AID = i.AID 
WHERE a.MCode = '10001'

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

You don't specify version of SQL Server. You can use the proprietary UPDATE...FROM syntax in all versions.

UPDATE ALAM
SET ALAM.CDate = IDCP.CDate
FROM ALAM
JOIN IDCP ON  ALAM.ASID = IDCP.ASID and ALAM.AID = IDCP.AID 
WHERE ALAM.MCode = '10001'

Or the more standard MERGE statement in SQL Server 2008.

Upvotes: 2

Related Questions