help with simple SQL update + join
I think this should be pretty simple, but I'm a SQL newb.
I have two tables. One is a list of items IDs and descriptions, the other is a map of corresponding old and new IDs. Like this:
ID_MAP
OLD_ID NEW_ID
---------------
1 101
2 102
ITEMS
ID DESCRIPTION
--------------------
1 "ItemA"
2 "ItemB"
...
101 <null>
102 <null>
I need to copy the old item descriptions to the new items according to the map. I think I need to use an inner join
inside of an update
, but it's not working and I'm not even sure that's the right way to go.
I'm trying statements like
update ITEMS
set (select ITEMS.DESCRIPTION
from ITEMS
join ID_MAP
on ITEMS.ID = ID_MAP.NEW_ID) =
(selec开发者_如何学编程t ITEMS.DESCRIPTION
from ITEMS
join ID_MAP
on ITEMS.ID = ID_MAP.OLD_ID)
But of course it's not working. What should I be doing?
update new_item
set description = old_item.description
from items old_item
inner join id_map im
on old_item.id = im.old_id
inner join items new_item
on im.new_id = new_item.id
Depending on if UPDATE .. FROM
is available in your DBMS (SQL Server vs Oracle) one possibility is using correlated sub-queries for each of your column updates. Not as convenient if you are able to do UPDATE FROM
.
UPDATE items i
SET i.id = (
SELECT new_id
FROM id_map
WHERE old_id = i.id
)
, description = (
SELECT description
FROM id_map
WHERE old_id = i.id
)
You could add the following to the end too
WHERE EXISTS (
SELECT 1
FROM id_map
WHERE old_id = id
)
精彩评论