Updating multiple records in oracle
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.
I can update multiple columns with a single UPDATE statement.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.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.
You need to be careful when using the UPDATE statement with a .
If you incorrectly specify your search condition you might update too many rows, or not enough rows.
I say random rows because TSQL doesn’t guarantee order unless you have an order by clause.
If you want to update two rows based on the ORDER BY clause then you will need to use the TOP clause in a sub query in conjunction with a WHERE constraint, like in the following example.
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.