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

onedaywhen · Accepted Answer · 2011-10-07 11:01:01Z

6

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;

answered Oct 7, 2011 at 11:01

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Smith · Accepted Answer · 2011-10-07 10:56:58Z

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.

answered Oct 7, 2011 at 10:56

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Comments

Tjekkles · Accepted Answer · 2011-10-07 10:59:39Z

1

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'

answered Oct 7, 2011 at 10:59

Tjekkles

5,6829 gold badges38 silver badges53 bronze badges

Comments

Paul Morgan · Accepted Answer · 2011-10-07 11:16:55Z

0

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'

answered Oct 7, 2011 at 11:16

Paul Morgan

32.8k3 gold badges29 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related