MS SQL Server Concepts and Programming Question: Download MS SQL Server PDF
How To Update Multiple Rows with One UPDATE Statement in MS SQL Server?
Answer:
If the WHERE clause in an UPDATE statement matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 ww.globalguideline.com NULL 0 2006-04-30
602 ww.globalguideline.com/html NULL 0 2007-05-19
603 ww.globalguideline.com/sql NULL NULL 2007-05-19
SELECT * FROM ggl_links
UPDATE ggl_links SET counts = 9, notes = 'Wrong'
WHERE id >= 500
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 ww.globalguideline.com Wrong 9 2006-04-30
602 ww.globalguideline.com/html Wrong 9 2007-05-19
603 ww.globalguideline.com/sql Wrong 9 2007-05-19
The UPDATE statement updated 3 rows with the same new values.
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 ww.globalguideline.com NULL 0 2006-04-30
602 ww.globalguideline.com/html NULL 0 2007-05-19
603 ww.globalguideline.com/sql NULL NULL 2007-05-19
SELECT * FROM ggl_links
UPDATE ggl_links SET counts = 9, notes = 'Wrong'
WHERE id >= 500
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 ww.globalguideline.com Wrong 9 2006-04-30
602 ww.globalguideline.com/html Wrong 9 2007-05-19
603 ww.globalguideline.com/sql Wrong 9 2007-05-19
The UPDATE statement updated 3 rows with the same new values.
| Previous Question | Next Question |
| How To Update Values in a Table with UPDATE Statements in MS SQL Server? | How to use old values to define new values in UPDATE statements in MS SQL Server? |