This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The UPDATE statement's job is to tentatively edit existing rows in a Table. If UPDATE succeeds, there will be a number (zero or more) of changed rows in your target Table. The required syntax for the UPDATE statement is as follows.

UPDATE [ ONLY ] <Table name> SET 
  <Column name>=scalar_expression
  [ {,<Column name>=scalar_expression} ... ] | 
[ WHERE <search condition> ]

UPDATE works by changing one or more values in zero or more rows of the target Table, Column by Column. The <Table reference> identifies your target Table: the Table that you want UPDATE to change rows of. The target Table must be updatable that is, it must either be a Base table, or a View that is not a read-only Table. If <Table reference> does not include a <Schema name> qualifier, your target Table must belong to the SQL-session default Schema. If your target Table is a typed Table, your UPDATE statement may include the optional <keyword> ONLY.

There are two forms of UPDATE: the first lets you specify multiple Column changes that don't necessarily change every value in a row, the second lets you specify changes to every value in a row with a single SET clause. To execute UPDATE, your current <AuthorizationID> needs the UPDATE Privilege on every Column directly affected by the update operation and, if an object Column is a derived Column (as it is in the case of a View), your current <AuthorizationID> also needs the UPDATE Privilege on every underlying Table that makes up your target Table or, if your target Table is a <joined Table> made with LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN or UNION JOIN, your current <AuthorizationID> also needs the INSERT Privilege on every underlying Table that makes up your target Table.


Comments loading...