CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

update multiple records with different where conditions

I have two tables which are used to deal with identifier changes.

So the table below is where identifiers are logged.

tblNewIds

DateFrom    OldId    NewId
2017-06-02  ABC      ABB
2017-04-21  XYZ      JHG

The next table is where all the daily sales are stored.

tblSales

DateSale    Id       
2017-01-01  ABC
2017-01-01  XYZ
2017-01-02  ABC
2017-01-02  XYZ 
...
2017-06-20  ABC
2017-06-20  XYZ

I want a query to update tblSales such that from 2017-04-21 any Id that equals XYZ changes to JHG & for from 2017-06-02 change ABC to ABB.

I know how I can do this for one record at a time with the update statement below but I would like to know how to do both at once?

update tblSales
set Id = 'ABB'
where Id = 'ABC' and DateSale >= '2017-06-02'

Upvotes: 2

Views: 74

Answers (3)

Frank
Frank

Reputation: 881

UPDATE tblSales
SET id=   CASE  
                        WHEN (Id = 'ABC' and DateSale >= '2017-06-02')  THEN 'ABB'  
                        WHEN (Id = 'XYZ' and DateSale >= '2017-04-21')  THEN 'JHG'  

                        END ;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You might be able to slightly modify your current update to use a CASE expression which can cover both types of update in a single statement.

update tblSales
set Id = case when Id = 'ABC' and DateSale >= '2017-06-02' then 'ABB'
              when Id = 'XYZ' and DateSale >= '2017-04-21' then 'JHG' END
where (Id = 'ABC' and DateSale >= '2017-06-02') or
      (Id = 'XYZ' and DateSale >= '2017-04-21')

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Assuming that ids are not chained, then you can do:

update s
    set id = ni.NewId
    from tblSales s join
         tblNewIds ni
         on s.id = ni.oldId and s.DateSale >= ni.DateFrom;

I would be cautious about making the change in the data, though. Losing the information about the original id could have unexpected side-effects.

If the ids can change more than once, I would suggest just running the update until there are no more changes. Although you can construct the correct id at a given point in time using a recursive CTE, it is a lot more work for a one-time effort.

Upvotes: 5

Related Questions