Update a table on multiple rows

• nadarajan_v

  SSC-Addicted

  Points: 406

  More actions

  February 25, 2014 at 9:48 am

  #290349

  Hi,

  I have an orders tables. From this table I have to retrieve all order Ids which are in the pending status. For these orders then I have to update vendor name and vendor address from the vendor table. Please let me know the best way to do it.

  Thanks

• EricEyster

  SSCrazy

  Points: 2974

  More actions

  February 25, 2014 at 9:53 am

  #1691970

  update Orders

  set O.address = V.address,

  etc

  from Orders O

  join Vendor V

  on =

  where O.Status = pending

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  February 25, 2014 at 9:54 am

  #1691971

  This is very simple, but you need to help us. Please post table definitions, sample data and expected results. For information on how to do it, read the article on my signature.

  It would be nice if you show us what have you done as it seems like homework.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286562

  More actions

  February 25, 2014 at 9:54 am

  #1691972

  nadarajan_v (2/25/2014)

  ---

  Hi,

  I have an orders tables. From this table I have to retrieve all order Ids which are in the pending status. For these orders then I have to update vendor name and vendor address from the vendor table. Please let me know the best way to do it.

  Thanks

  With a join.

  The real question is why are you storing the vendor name and address in your order table? You should store the PK of the vendor only in your order table. Otherwise your vendor information is possible to be out of synch when the vendor information changes.

  Since you didn't provide any details the best I can do is pseudocode.

  UPDATE o

  set VendorName = v.VendorName,

  VendorAddress = v.VendorAddress

  from [Order] o --Shouldn't use reserved words for object names

  join Vendors v on v.VendorID = o.VendorID

  where o.Status = 'Pending'

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/