SQL Update Multiple Fields FROM via a SELECT Statement?

Something like this should work (can't test it right now - from memory).

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.

Works like a charm, I knew there had to be a way to do an update based a select statement but how was escaping me. – Christopher Kelly Aug 12 '09 at 20:26 FYI - this doesn't work in oracle. Instead, you can use update ( select table1.

Field1 as a1 , table2. Field2 as b1 from table1, table2 where field1 = field2) set a1 = b1 note: I think the aliases are necessary if your fields have the same name for some reason. – Jody Nov 5 '10 at 15:25 1 @Jody: the question was about SQL Server - not Oracle - so I gave a solution that works in SQL Server.

– marc_s Nov 5 '10 at 16:24.

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.

Same difference. It's cleaner to me. I'm not double selecting.

I'm getting only the results from s as the come from the FROM clause. – Josh Aug 12 '09 at 19:22 Well there was a question about why is specify Shipment in the FROM clause instead of near after update. Marc_s answer above and this actually have the exact same execution plan in MSSQL – Josh Aug 12 '09 at 19:26.

You can use update from... something like: update shipment set.... from shipment inner join ProfilerTest.dbo. BookingDetails on ...

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.

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.

I'm not sure given the original example there is a one to one (or many) relationship between BookingDetails and Shipment for bd. MyID to s. MyID2.

Example given has (admittingly I'm assuming) two different paramters for ids, so I'm assuming the potential for them to be differnet. I agree though that if an eplicit join exists it should be used! – Josh Aug 12 '09 at 20:07 Didn't even notice that, so perhaps the cross join is the better solution.

Only the OP knows for sure. – HLGEM Aug 12 '09 at 20:43.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.