SQL 通过 SELECT 语句更新多个字段 FROM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1267993/
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 Multiple Fields FROM via a SELECT Statement
提问by KellCOMnet
This works, but i would like to remove the redundancy. Is there a way to merge the update with a single select statement so i don't have to use vars?
这有效，但我想删除冗余。有没有办法将更新与单个选择语句合并，这样我就不必使用 vars？
DECLARE
@OrgAddress1 varchar,
@OrgAddress2 varchar,
@OrgCity varchar,
@OrgState varchar,
@OrgZip varchar,
@DestAddress1 varchar,
@DestAddress2 varchar,
@DestCity varchar,
@DestState varchar,
@DestZip varchar
SELECT
@OrgAddress1 = OrgAddress,
@OrgAddress2 = OrgAddress2,
@OrgCity = OrgCity,
@OrgState = OrgState,
@OrgZip = OrgZip,
@DestAddress1 = DestAddress,
@DestAddress2 = DestAddress2,
@DestCity = DestCity,
@DestState = DestState,
@DestZip = DestZip
FROM
ProfilerTest.dbo.BookingDetails
WHERE
MyID=@MyID
UPDATE SHIPMENT
SET
OrgAddress1 = @OrgAddress1,
OrgAddress2 = @OrgAddress2,
OrgCity = @OrgCity,
OrgState = @OrgState,
OrgZip = @OrgZip,
DestAddress1 = @DestAddress1,
DestAddress2 = @DestAddress2,
DestCity = @DestCity,
DestState = @DestState,
DestZip = @DestZip
WHERE
MyID2=@ MyID2
回答by marc_s
Something like this should work (can't test it right now - from memory):
这样的事情应该可以工作（现在无法测试 - 从记忆中）：
UPDATE SHIPMENT
SET
OrgAddress1 = BD.OrgAddress1,
OrgAddress2 = BD.OrgAddress2,
OrgCity = BD.OrgCity,
OrgState = BD.OrgState,
OrgZip = BD.OrgZip,
DestAddress1 = BD.DestAddress1,
DestAddress2 = BD.DestAddress2,
DestCity = BD.DestCity,
DestState = BD.DestState,
DestZip = BD.DestZip
FROM
BookingDetails BD
WHERE
SHIPMENT.MyID2 = @MyID2
AND
BD.MyID = @MyID
Does that help?
这有帮助吗？
回答by eKek0
You can use:
您可以使用：
UPDATE s SET
s.Field1 = q.Field1,
s.Field2 = q.Field2,
(list of fields...)
FROM (
SELECT Field1, Field2, (list of fields...)
FROM ProfilerTest.dbo.BookingDetails
WHERE MyID=@MyID
) q
WHERE s.MyID2=@ MyID2
回答by Joshua Shannon
You should be able to do something along the lines of the following
您应该能够按照以下方式做一些事情
UPDATE s
SET
OrgAddress1 = bd.OrgAddress1,
OrgAddress2 = bd.OrgAddress2,
...
DestZip = bd.DestZip
FROM
Shipment s, ProfilerTest.dbo.BookingDetails bd
WHERE
bd.MyID = @MyId AND s.MyID2 = @MyID2
FROM statement can be made more optimial (using more specific joins), but the above should do the trick. Also, a nice side benefit to writing it this way, to see a preview of the UPDATE change UPDATE s SETto read SELECT! You will then see that data as it would appear if the update had taken place.
FROM 语句可以变得更优化（使用更具体的连接），但上面的应该可以解决问题。此外，以这种方式编写它还有一个很好的附带好处，即查看 UPDATE 更改UPDATE s SET为 read的预览SELECT！然后，您将看到更新发生后的数据。
回答by Joshua Shannon
you can use update from...
您可以使用更新...
something like:
就像是：
update shipment set.... from shipment inner join ProfilerTest.dbo.BookingDetails on ...
更新装运集.... 从装运内部加入 ProfilerTest.dbo.BookingDetails on ...
回答by HLGEM
I would write it this way
我会这样写
UPDATE s
SET OrgAddress1 = bd.OrgAddress1, OrgAddress2 = bd.OrgAddress2,
... DestZip = bd.DestZip
--select s.OrgAddress1, bd.OrgAddress1, s.OrgAddress2, bd.OrgAddress2, etc
FROM Shipment s
JOIN ProfilerTest.dbo.BookingDetails bd on bd.MyID =s.MyID2
WHERE bd.MyID = @MyId
This way the join is explicit as implicit joins are a bad thing IMHO. You can run the commented out select (usually I specify the fields I'm updating old and new values next to each other) to make sure that what I am going to update is exactly what I meant to update.
这样连接是显式的，因为隐式连接是一件坏事恕我直言。您可以运行注释掉的选择（通常我指定我要更新彼此相邻的旧值和新值的字段）以确保我要更新的内容正是我想要更新的内容。
回答by KenF
I just had to solve a similar problem where I added a Sequence number (so that items as grouped by a parent ID, have a Sequence that I can order by (and presumably the user can change the sequence number to change the ordering).
我只需要解决一个类似的问题，我添加了一个序列号（以便按父 ID 分组的项目有一个我可以订购的序列（并且大概用户可以更改序列号来更改排序）。
In my case, it's insurance for a Patient, and the user gets to set the order they are assigned, so just going by the primary key isn't useful for long-term, but is useful for setting a default.
在我的情况下，它是对患者的保险，用户可以设置分配给他们的顺序，因此仅按主键对长期没有用，但对设置默认值很有用。
The problem with all the other solutions is that certain aggregate functions aren't allowed outside of a SELECT
所有其他解决方案的问题是在 SELECT 之外不允许某些聚合函数
This SELECT gets you the new Sequence number:
这个 SELECT 为您提供新的序列号：
select PatientID,
PatientInsuranceID,
Sequence,
Row_Number() over(partition by PatientID order by PatientInsuranceID) as RowNum
from PatientInsurance
order by PatientID, PatientInsuranceID
This update command, would be simple, but isn't allowed:
这个更新命令很简单，但不允许：
update PatientInsurance
set Sequence = Row_Number() over(partition by PatientID order by PatientInsuranceID)
The solution that worked (I just did it), and is similar to eKek0's solution:
有效的解决方案（我刚刚做到了），并且类似于 eKek0 的解决方案：
UPDATE PatientInsurance
SET PatientInsurance.Sequence = q.RowNum
FROM (select PatientInsuranceID,
Row_Number() over(partition by PatientID order by PatientInsuranceID) as RowNum
from PatientInsurance
) as q
WHERE PatientInsurance.PatientInsuranceID=q.PatientInsuranceID
this lets me select the ID I need to match things up to, and the value I need to set for that ID. Other solutions would have been fine IF I wasn't using Row_Number() which won't work outside of a SELECT.
这让我可以选择我需要匹配的 ID，以及我需要为该 ID 设置的值。如果我没有使用 Row_Number()，它在 SELECT 之外无法工作，那么其他解决方案会很好。
Given that this is a 1 time operation, it's coding is still simple, and run-speed is fast enough for 4000+ rows
鉴于这是一个 1 次操作，它的编码仍然很简单，并且运行速度对于 4000+ 行来说足够快
