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, 5564👍, 0💬

How To Insert Multiple Rows with One INSERT Statement
How To Insert Multiple Rows with One INSERT Statement? - Oracle DBA FAQ - Understanding SQL DML Statements If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The f... 2007-04-21, 5817👍, 0💬

How To Delete Multiple Rows from a Table
How To Delete Multiple Rows from a Table? - Oracle DBA FAQ - Understanding SQL DML Statements You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the fyi_links table... 2007-04-21, 5667👍, 0💬

How To Update Values on Multiple Rows
How To Update Values on Multiple Rows? - Oracle DBA FAQ - Understanding SQL DML Statements 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 e... 2007-04-21, 5565👍, 0💬

How To Delete an Existing Row from a Table
How To Delete an Existing Row from a Table? - Oracle DBA FAQ - Understanding SQL DML Statements If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements: INSERT INTO fyi_links (url, id) VAL... 2007-04-21, 5296👍, 0💬

How To Use Values from Other Tables in UPDATE Statements
How To Use Values from Other Tables in UPDATE Statements? - Oracle DBA FAQ - Understanding SQL DML Statements If you want to update values in one with values from another table, you can use a subquery in the SET clause. The subquery should return only one row for each row in the update table that ma... 2007-04-21, 5248👍, 0💬

How To Specify Default Values in INSERT Statement
How To Specify Default Values in INSERT Statement? - Oracle DBA FAQ - Understanding SQL DML Statements If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a g... 2007-04-21, 5238👍, 0💬

How To Use Existing Values in UPDATE Statements
How To Use Existing Values in UPDATE Statements? - Oracle DBA FAQ - Understanding SQL DML Statements If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by columns in ... 2007-04-21, 5206👍, 0💬

How To Update Values in a Table
How To Update Values in a Table? - Oracle DBA FAQ - Understanding SQL DML Statements If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The script below shows a good example: UPDATE fyi_links SET counts = 999, notes = 'Good site.' WHERE id = 1... 2007-04-21, 5159👍, 0💬

How To Insert a New Row into a Table
How To Insert a New Row into a Table? - Oracle DBA FAQ - Understanding SQL DML Statements To insert a new row into a table, you should use the INSERT INTO statement with values specified for all columns as shown in the following example: INSERT INTO fyi_links VALUES (101, 'http://dev.fyicenter.com',... 2007-04-21, 4968👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.