UPDATE

SQL command to update rows in a table.

Syntax

Parameters

tableName

The name of the target table

columnName

The name of one of the target column in the target table

literal

A literal whose type must be compatible with the target column

identifier

A database identifier whose type must be compatible with the target column

predicate

A predicate which should evaluate to true for the given row to be updated

Examples

Update a column

SELECT * FROM T;

A	B	C
1	"one"	1.0
2	"two"	2.0
3	"three"	NULL
4	"four"	NULL

UPDATE T SET C = 20;

SELECT * FROM T;

A	B	C
1	"one"	20.0
2	"two"	20.0
3	"three"	20.0
4	"four"	20.0

Update a column for rows that match a certain predicate

SELECT * FROM T;

A	B	C
1	"one"	1.0
2	"two"	2.0
3	"three"	NULL
4	"four"	NULL

UPDATE T SET C = NULL WHERE C IS NOT NULL;

SELECT * FROM T;

A	B	C
1	"one"	NULL
2	"two"	NULL
3	"three"	NULL
4	"four"	NULL

Update multiple columns

SELECT * FROM T;

A	B	C
1	"one"	1.0
2	"two"	2.0
3	"three"	NULL
4	"four"	NULL

UPDATE T SET B = 'zero', C = 0.0 WHERE C IS NULL;

SELECT * FROM T;

A	B	C
1	"one"	1.0
2	"two"	2.0
3	"zero"	0.0
4	"zero"	0.0

Update field inside a STRUCT

SELECT * FROM T;

A	B	C
1	"one"	{ "S1": 10 , "S2": 100 }
2	"two"	{ "S1": 20 , "S2": 200 }

UPDATE T SET C.S1 = 45 WHERE C.S2 = 200;

SELECT * FROM T;

A	B	C
1	"one"	{ "S1": 10 , "S2": 100 }
2	"two"	{ "S1": 45 , "S2": 200 }

Update STRUCT field

SELECT * FROM T;

A	B	C
1	"one"	{ "S1": 10 , "S2": 100 }
2	"two"	{ "S1": 20 , "S2": 200 }

UPDATE T SET C = (0, 0) WHERE A = 2;

SELECT * FROM T;

A	B	C
1	"one"	{ "S1": 10 , "S2": 100 }
2	"two"	{ "S1": 0 , "S2": 0 }