SQL Update Multiple Fields FROM via a SELECT Statement

Question

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?

 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

Answer 1

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??

Marc

Answer 2

you can use update from...

something like:

update shipment set.... from shipment inner join ProfilerTest.dbo.BookingDetails on ...

Answer 3

You can use:

UPDATE s FROM
  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

Answer 4

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 SET to read SELECT! You will then see that data as it would appear if the update had taken place.

Answer 5

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.