Reputation: 6668
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
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
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
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