SQL 通过 SELECT 语句更新多个字段

本教程将介绍SQL 通过 SELECT 语句更新多个字段的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

SQL 通过 SELECT 语句更新多个字段 教程 第1张

问题描述

这可行,但我想删除冗余.有什么办法将更新与单个 select 语句合并,这样我就不必使用 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

推荐答案

这样的东西应该可以工作(现在无法测试 - 凭记忆):

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

这有帮助吗?

好了关于SQL 通过 SELECT 语句更新多个字段的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。