UPDATE

Use the UPDATE statement to update existing records in a table.

Syntax

{
 UPDATE table-Name
   [[AS] correlation-Name]
   SET column-Name = Value
       [ , column-Name = Value} ]*
   [WHERE clause]
}

table-Name

The name of the table to update.

correlation-Name

An optional correlation name for the update.

column-Name = Value

Sets the value of the named column to the named value in any records .

Value is either an Expression or the literal DEFAULT. If you specify DEFAULT for a column’s value, the value is set to the default defined for the column in the table.

The DEFAULT literal is the only value that you can directly assign to a generated column. Whenever you alter the value of a column referenced by the generation-clause of a generated column, Splice Machine recalculates the value of the generated column.

WHERE clause

Specifies the records to be updated.

Example

This example updates the Birthdate value for a specific player:

splice> UPDATE Players
   SET Birthdate='03/27/1987'
   WHERE DisplayName='Buddy Painter';
1 row inserted/updated/deleted

This example updates the team name associated with all players on the Giants team:

splice> UPDATE Players
   SET Team='SFGiants'
   WHERE Team='Giants';
48 rows inserted/updated/deleted

This example updates two columns in a table by selecting values from another table:

splice> CREATE TABLE table_a (column2 INT, column3 INT);
0 rows inserted/updated/deleted
splice> CREATE TABLE table_b (columnx INT, columny INT);
0 rows inserted/updated/deleted
splice> INSERT INTO table_b VALUES (1,1),(2,2);
2 rows inserted/updated/deleted
splice> INSERT INTO table_a VALUES (1,10),(2,20);
2 rows inserted/updated/deleted

splice> UPDATE table_a SET (column2, column3) = (SELECT columnx, columny FROM table_b AS b WHERE table_a.column2 = b.columnx);
2 rows inserted/updated/deleted

splice> select * from table_a;
COLUMN2|COLUMN3
---------------
1      |1
2      |2

Statement dependency system

A searched update statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), all of its constraints, and any other table named in the DROP INDEX statement or an  ALTER TABLE statement for the target table of a prepared searched update statement invalidates the prepared searched update statement.

A CREATE or DROP INDEX statement or an ALTER TABLE statement for the target table of a prepared positioned update invalidates the prepared positioned update statement.

Dropping an alias invalidates a prepared update statement if the latter statement uses the alias.

Dropping or adding triggers on the target table of the update invalidates the update statement.

See Also