MySQL SQL Error more then 1 table select

Question

我正在尝试编写一个检查多个表的 sql，这就是我得到的:

UPDATE item_template SET BuyPrice = 0, SellPrice = 1 WHERE entry IN (SELECT item FROM npc_vendor WHERE entry IN (SELECT entry FROM creature WHERE area = 3998)));

现在我的错误是:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

可能是什么原因造成的？

Answer 1

正如 Dave Mroz 提到的，如果您使用 multi-table update syntax，此查询会更有效使用 JOIN，像这样:

UPDATE item_template 
  inner join npc_vendor on npc_vendor.item = item_template.entry
  inner join creature on creature.entry = npc_vendor.entry
SET item_template.BuyPrice = 0, item_template.SellPrice = 1 
where creature.area = 3998;