Updating tables in sql server
I can do that with the following code: In this code I updated the Toy table based on values in another table, in this case a table named New Toy Price.
To accomplish that I joined the TOY table to my New Toy Price table based on the ID column.
I then used the New Toy Price column values for Toy Name and Price to update my Toy table column values on rows that have matching column ID values.
We have already seen how to limit the rows being updated by using the WHERE clause.
There may be times when you don’t want to manually write a bunch of UPDATE statements with different literal strings to update your table.
Suppose I wanted to change all the prices of my Toys with a single UPDATE statement.
Below is the TSQL code to create and populate this table.
These two different tables will be used in my UDPATE statement examples below.
I can update multiple columns with a single UPDATE statement.
You can also limit the rows being updated using the TOP clause.
Suppose you want to update only two rows with a new price then you can run the following code: This code updates two random rows in my Toy table.
By doing this I can see what my WHERE condition will return to make sure it identifies the same rows I want to update.
Once my SELECT statement does return the correct rows I can then copy the WHERE clause from my SELECT statement, and paste it into my UDPATE statement code.
Below is an example that does this: I updated the Toy Name and the Price for the Toy row with an ID value of 4, which was my original Super Surfer row.