使用select在同一个表上更新行

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         |

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

我想：每排;如果TEXT为NULL;使用具有相同ID且LANG ='EN'的行的TEXT值更新它。

What is the SQL request to do something like that ?

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

4 个解决方案

#1

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

您没有指定数据库。以下是标准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;

EDIT:

编辑：

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

当然，并非所有数据库都支持所有ANSI标准功能。在MySQL中，您将使用连接：

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;

#2

For MS SQL you can use a join to do this, it might not work with other databases though:

对于MS SQL，您可以使用联接来执行此操作，但它可能不适用于其他数据库：

UPDATE t1
SET t1.text = t2.text
FROM table1 t1
INNER JOIN table1 t2 ON t1.id = t2.id 
WHERE t1.TEXT IS NULL AND t2.LANG = 'EN'

Sample SQL Fiddle

示例SQL小提琴

#3

UPDATE TABLE
SET TEXT = (SELECT TOP 1 text WHERE id = table.id AND LANG ='EN' AND TEXT IS NOT NULL) --TOP 1 INCASE YOU HAVE DUPLICATES
WHERE TEXT IS NULL

#4

Update Test T1  INNER JOIN (SELECT T2.ID AS "id",MAX(T2.TEXTV) AS "MTEXT" FROM TEST T2
                             WHERE T2.LANG = 'EN'
                             AND T2.TEXTV IS NOT NULL   
                             GROUP BY T2.ID ) AS DER          
            ON T1.ID = DER.id

SET T1.TEXTV = DER.MTEXT
WHERE T1.TEXTV IS NULL ;   ------MySQL Query

#1