MS SQL Server Interview Question:

How To Update Multiple Rows with One UPDATE Statement in MS SQL Server?

Submitted by: Administrator

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.
Submitted by: Administrator