在同一张表上使用 select 更新行 [英] Update row with select on same table

问题描述

我正在尝试使用同一个表查询更新行.背景:

I'm trying to update row with same table query. Context:

ID        |  LANG       |  TEXT
----------------------------------
1         |  EN         |  Hello
1         |  FR         |
1         |  ES         |
2         |  EN         |  Boat
2         |  FR         |  Bateau
2         |  ES         |

我想:对于每一行；如果 文本为空;使用具有相同 ID 和 LANG = 'EN' 的行的 TEXT 值更新它.

I want to : For each row; if TEXT IS NULL; update it with TEXT value of row with same ID and LANG = 'EN'.

执行类似操作的 SQL 请求是什么?

What is the SQL request to do something like that ?

推荐答案

你没有指定数据库.以下是标准SQL:

You don't specify the database. The following is standard SQL:

UPDATE t
    SET TEXT = (SELECT text
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

如果出现重复，以下应该有效:

In the event of duplicates, the following should work:

UPDATE t
    SET TEXT = (SELECT max(text)
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

当然，并非所有数据库都支持所有 ANSI 标准功能.在 MySQL 中，您将使用 join 代替:

Of course, not all databases support all ANSI standard functionality. In MySQL, you would use a join instead:

UPDATE t JOIN
       (SELECT id, max(text) as text_en
        FROM t t2
        WHERE LANG ='EN' AND TEXT IS NOT NULL
       ) ten 
       ON t.id = ten.id
    SET t.TEXT = ten.text_en
    WHERE t.TEXT IS NULL;

这篇关于在同一张表上使用 select 更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！