UPDATE random result numbers to same numbers result

Question

i'm trying to update column from another table which means :

SELECT DISTINCT id FROM creature WHERE map = 389;

This SQL will give me this result :

11323
11322
11324
11520
11321

What i want is to update creature_template lootid = 11323 where entry = 11323 so it goes as following :

UPDATE creature_template SET lootid = 11323 WHERE entry = 11323

I have tried this :

UPDATE creature_template SET lootid = 
    (SELECT DISTINCT id
    FROM creature
    WHERE map = 389) WHERE lootid = entry;

I'm sure it's incorrect simply it's not logic but couldn't find the logical answer for this.

Even REPLACE could work instead of UPDATE so any will work.

Answer 1

You need JOIN with UPDATE :

UPDATE creature_template ct 
     INNER JOIN creature c
     ON c.id = ct.entry 
     SET ct.lootid  = c.id
WHERE c.map = 389;