SQL 使用同一张表上的选择更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25266878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update row with select on same table
提问by bux
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'.
我想：对于每一行；如果文本为空；用具有相同 ID 和 LANG = 'EN' 的行的 TEXT 值更新它。
What is the SQL request to do something like that ?
执行此类操作的 SQL 请求是什么？
回答by Gordon Linoff
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 joininstead:
当然，并非所有数据库都支持所有 ANSI 标准功能。在 MySQL 中，您可以使用 ajoin代替：
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;
回答by jpw
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'
回答by gh9
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
回答by aman
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
