Updating Multiple Rows with One UPDATE Statement in SQL Server

Q

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

✍: FYIcenter.com

A

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 fyi_links WHERE id >= 500
GO
id      url                     notes   counts  created
601     moc.retneciyf.ved       NULL    0       2006-04-30
602     moc.retneciyf.abd       NULL    0       2007-05-19
603     moc.retneciyf.aqs       NULL    NULL    2007-05-19

SELECT * FROM fyi_links
UPDATE fyi_links SET counts = 9, notes = 'Wrong' 
   WHERE id >= 500
(3 row(s) affected)

SELECT * FROM fyi_links WHERE id >= 500
GO
id      url                     notes   counts  created
601     moc.retneciyf.ved       Wrong   9       2006-04-30
602     moc.retneciyf.abd       Wrong   9       2007-05-19
603     moc.retneciyf.aqs       Wrong   9       2007-05-19

The UPDATE statement updated 3 rows with the same new values.

 

Using INSERT, UPDATE and DELETE Statements in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-02, 550👍, 0💬