Sql updating table from same table
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.I did this by having a single SET clause in my UPDATE statement, with the two column name/value pairs separated with a comma.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.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.Below is the TSQL code to create and populate this table.
We have already seen how to limit the rows being updated by using the WHERE clause.
If you want to review the complete syntax of the UPDATE statement then please refer to Books Online.
To properly show you how to use the UPDATE statement I will need to create a few tables to hold some sample data. Below is the code to create my Toy table: In order to show you how to UPDATE a table from data in another table I need to build a second sample data table that I will call New Toy Price.
By doing this I have made sure I don’t have a bogus WHERE clause on my UPDATE statement that is going to incorrectly identify rows to be updated in my table.
In my examples above I only updated a single column with my UPDATE statement.