Updating multiple columns in a table based on another table

• selaine

  SSC Enthusiast

  Points: 149

  More actions

  October 26, 2010 at 12:07 pm

  #225453

  Sorry but have another SQL dilemna and desperately need your

  help. I'm new to SQL and I'm still trying to figure things out.

  First of all, I'm not sure if I should be using an UPDATE statement or

  an INSERT INTO statementI basically need to grab values from columns

  in the VoteCenters Table and place them into a column with the same

  name in the PollingPlaces column IF the value of the CodePlace column

  in the PollingPlaces.table = the CodePlace column in the VoteCenters

  table.

  All of these columns (PLACE, VCFullAddr, VCAddr, VCCity, VCZip, Phone,

  AliasLink) in all records in the PollingPlaces table need to be

  updated and I'd like to do it in the most efficient way possible. I

  realized I could simply do one field at a time, but that doesn't seem

  efficient to me.

  I've written the following but there's a problem with the syntax.

  INSERT INTO PollingPlaces(PLACE, VCFullAddr, VCAddr, VCCity, VCZip,

  Phone, AliasLink)

  Select VoteCenters.PLACE, VoteCenters.VCFullAddr, VoteCenters.VCAddr,

  VoteCenters.VCCity, VoteCenters.VCZip, VoteCenters.Phone,

  VoteCenters.AliasLink

  FROM VoteCenters

  Left Outer Join

  PollingPlaces

  on PollingPlaces.CodePlace = VoteCenters.CodePlace

  WHERE PollingPlaces.CodePlace IS NULL or PollingPlaces.CodePlace = ' '

  Thank you for your time.

• Will.Graham

  SSC Rookie

  Points: 34

  More actions

  October 26, 2010 at 12:14 pm

  #1242490

  You can use an UPDATE FROM to accomplish this.

  For example:

  UPDATE Polling

  SET Polling.X = VC.X, Polling.Y = VC.Y

  FROM PollingCenters AS Polling

  LEFT OUTER JOIN VotingCenters VC ON Polling.CodePlace = VC.CodePlace

  This will update the PollingCenters table with the values from the VotingCenters table if the CodePlace matches.

  SQL newbie here so DBAs/experts feel free to correct me.