是否有可能使用另一个嵌套查询来更新带有结果的表，该查询包含查询表本身？

I have two tables. First has id (AI) field and old_id (Int) field. Second table has a referred field p_id from first tables old_id as a relation point.

我有两张桌子。首先有id(AI)字段和old_id(Int)字段。第二表具有来自第一表old_id的引用字段p_id作为关系点。

I want to update p_id to firstTable.id values. Is it possible to update secondTable.p_id with the value returned from firstTable.id ?

我想将p_id更新为firstTable.id值。是否可以使用firstTable.id返回的值更新secondTable.p_id?

Here is my test sql:

这是我的测试sql:

UPDATE secondTable sT 
SET sT.p_id = (
    SELECT fT.id 
    FROM firstTable fT 
    WHERE fT.old_id = secondTable.p_id
)

1 个解决方案

#1

You can update with JOIN like this:

您可以使用JOIN更新,如下所示:

UPDATE secondTable sT 
INNER JOIN firstTable fT ON fT.old_id=secondTable.p_id
SET sT.p_id = fT.id;