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

User · Answer

you can use update from     something like   update shipment set     from shipment inner join ProfilerTest dbo BookingDetails on

User · Answer

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

User · Answer

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    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  This SELECT gets you the new Sequence number   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   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   Given that this is a 1 time operation  it s coding is still simple  and run-speed is fast enough for 4000  rows

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Update Multiple Fields FROM via a SELECT Statement

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures

Examples related to sql-update