使用同一张表上的选择更新行 [英] 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;
这篇关于使用同一张表上的选择更新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文