How To Update Values on Multiple Rows

Q

How To Update Values on Multiple Rows? - Oracle DBA FAQ - Understanding SQL DML Statements

✍: FYIcenter.com

A

If the WHERE clause in an UPDATE 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:

UPDATE fyi_links SET counts = 9, notes = 'Wrong URL' 
  WHERE id >= 250;
3 rows updated.

SELECT * FROM fyi_links WHERE id >= 250;
   ID URL                  NOTES         COUNTS CREATED
----- -------------------- ------------ ------- ---------
  250 Retail Sales.com     Wrong URL          9 07-MAY-06
  260 Recruiting.com       Wrong URL          9 07-MAY-06
  270 Payroll.com          Wrong URL          9 07-MAY-06

This statement updated 3 rows with the same new values on all 3 rows.

2007-04-21, 5308👍, 0💬