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
)