UPDATE¶

You can update the column value of a record stored in the target table to a new one by using the UPDATE statement. Specify the name of the column to update and a new value in the SET clause, and specify the condition to be used to extract the record to be updated in the WHERE Clause. You can one table only with one UPDATE statement.

UPDATE table_name SET column_name = {expr | DEFAULT} [, column_name = {expr | DEFAULT} ...]
    [WHERE search_condition]
    [LIMIT row_count]

• column_name: Specifies the column name to be updated. Columns for one or more tables can be specified.
• expr | DEFAULT: Specifies a new value for the column and expression or DEFAULT keyword can be specified as a value. The SELECT statement returning result record also can be specified.
• search_condition: Update only data that meets the search_condition if conditions are specified in the WHERE Clause.
• row_count: Specifies the number of records to be updated after the LIMIT Clause. An integer greater than 0 can be specified.

You can limit the number of records to be updated in the LIMIT Clause. You can use the update with the ORDER BY Clause if you want to maintain the execution order or lock order of triggers.

The following example shows how to update one table.

--creating a new table having all records copied from a_tbl1
CREATE TABLE a_tbl5 AS SELECT * FROM a_tbl1;
SELECT * FROM a_tbl5 WHERE name IS NULL;

           id  name                  phone
=========================================================
         NULL  NULL                  '000-0000'
            4  NULL                  '000-0000'
            5  NULL                  '000-0000'
            7  NULL                  '777-7777'

UPDATE a_tbl5 SET name='yyy', phone='999-9999' WHERE name IS NULL LIMIT 3;
SELECT * FROM a_tbl5;

           id  name                  phone
=========================================================
         NULL  'yyy'                 '999-9999'
            1  'aaa'                 '000-0000'
            2  'bbb'                 '000-0000'
            3  'ccc'                 '333-3333'
            4  'yyy'                 '999-9999'
            5  'yyy'                 '999-9999'
            6  'eee'                 '000-0000'
            7  NULL                  '777-7777'