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

Question

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

Solution 1

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;

Solution 2

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.

Solution 3

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'