SQL UPDATE SELECT 与 WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45751642/
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
SQL UPDATE SELECT with WHERE
提问by user1673665
I want to UPDATEa column in a table, but in each row there must go another value dependend on a WHEREof another row.
我想要UPDATE表中的一列，但在每一行中必须有另一个值依赖于WHERE另一行的 a。
This is how the table looks like.
这就是桌子的样子。
BusinessUnitGUID | ClassName | DefaultGUID
业务单位GUID | 类名 | 默认GUID
5 | PriceList | 349FDAFD34M
5 | Footer1 | 987IOXG376L
5 | Header1 | 12WQX954MIO
7 | PriceList | NULL
7 | Footer1 | NULL
7 | Header1 | NULL
Results should be this.
结果应该是这样。
BusinessUnitGUID | ClassName | DefaultGUID
业务单位GUID | 类名 | 默认GUID
5 | PriceList | 349FDAFD34M
5 | Footer1 | 987IOXG376L
5 | Header1 | 12WQX954MIO
7 | PriceList | 349FDAFD34M
7 | Footer1 | 987IOXG376L
7 | Header1 | 12WQX954MIO
But this shown query doesn't work, because it returns many rows and so it's not precise.
但是这个显示的查询不起作用，因为它返回很多行，所以它不精确。
update cSC_BusinessUnit
set defaultguid =
(
select defaultguid
from cSC_BusinessUnit
where BusinessUnitGUID = 5
)
where BusinessUnitGUID = 7
回答by waka
You need to do a check on ClassName as well:
您还需要检查 ClassName：
update b1
set b1.defaultguid =
(
select b2.defaultguid
from cSC_BusinessUnit b2
where b2.BusinessUnitGUID = 5
AND b2.ClassName = b1.ClassName
)
from cSC_BusinessUnit b1
where b1.BusinessUnitGUID = 7
回答by Aman Prajapati
Use below short version as well
也使用以下简短版本
UPDATE b1
SET b1.defaultguid = b2.defaultguid
FROM cSC_BusinessUnit b1
JOIN
cSC_BusinessUnit b2
ON b1.ClassName = b2.ClassName
And b2.BusinessUnitGUID = 5
WHERE b1.BusinessUnitGUID = 7
回答by user2877959
Does this solve your problem?
这能解决您的问题吗？
update cSC_BusinessUnit t1
set defaultguid =
(
select defaultguid
from cSC_BusinessUnit t2
where t2.BusinessUnitGUID = 5
and t1.classname = t2.classname
)
where BusinessUnitGUID = 7
回答by Misery
You can also set dynamically all the NULL columns like this as long as you have only one occurence of ClassNameNOT NULL
您也可以像这样动态设置所有 NULL 列，只要您只出现一次ClassNameNOT NULL
update A
set DefaultGUID =
(
select B.DefaultGUID
from cSC_BusinessUnit B
where B.ClassName = A.ClassName
And B.DefaultGUID IS NOT NULL
)
from cSC_BusinessUnit A
回答by Hung Le
You can try it
你可以试试看
update cSC_BusinessUnit a
set a.defaultguid =
(
select defaultguid
from cSC_BusinessUnit b
where b.BusinessUnitGUID = 5 and b.ClassName = a.ClassName
)
where a.BusinessUnitGUID = 7
回答by Vijay dubey
update b1
set b1.defaultguid =
(
select b2.defaultguid
from cSC_BusinessUnit b2
where b2.BusinessUnitGUID = 5
AND b2.ClassName = b1.ClassName
)
from cSC_BusinessUnit b1
where b1.BusinessUnitGUID = 7
