Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

UPDATE

Description

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 also specify 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.

Syntax

UPDATE table_name SET column_name = {expr | DEFAULT} [, column_name = {expr | DEFAULT}...]

    [WHERE search_condition]

    [ORDER BY {col_name | expr}]

    [LIMIT row_count]

  • table_name: Specifies the name of the table to be updated.
  • column_name: Specifies the columns to be updated.
  • expr | DEFAULT: Specifies a new value for the column, and specify an expression or the DEFAULT keyword as the value. You can also specify the SELECT query, which returns a single result record.
  • search_condition: You can update the column value only for the record that satisfies the condition by specifying one in the WHERE Clause.
  • col_name | expr: Specifies a column used as a basis for the update order.
  • row_count: Specifies the number of records to be updated after the LIMIT Clause. An integer greater than 0 can be specified.
Remark

One column can be updated only once in the same UPDATE statement.

Example

--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'

 

-- using triggers, that the order in which the rows are updated is modified by the ORDER BY clause.

 

CREATE TABLE t (i INT,d INT);

CREATE TRIGGER trigger1 BEFORE UPDATE ON t IF new.i < 10 EXECUTE PRINT 'trigger1 executed';

CREATE TRIGGER trigger2 BEFORE UPDATE ON t IF new.i > 10 EXECUTE PRINT 'trigger2 executed';

INSERT INTO t VALUES (15,1),(8,0),(11,2),(16,1), (6,0),(1311,3),(3,0);

UPDATE t  SET i = i + 1 WHERE 1 = 1;

 

trigger2 executed

trigger1 executed

trigger2 executed

trigger2 executed

trigger1 executed

trigger2 executed

trigger1 executed

 

TRUNCATE TABLE t;

INSERT INTO t VALUES (15,1),(8,0),(11,2),(16,1), (6,0),(1311,3),(3,0);

UPDATE t SET i = i + 1 WHERE 1 = 1  ORDER BY i;

 

trigger1 executed

trigger1 executed

trigger1 executed

trigger2 executed

trigger2 executed

trigger2 executed

trigger2 executed