5
CREATE TABLE update_test (
11
INSERT INTO update_test VALUES (5, 10, 'foo');
12
INSERT INTO update_test(b, a) VALUES (15, 10);
14
SELECT * FROM update_test;
16
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
18
SELECT * FROM update_test;
20
-- aliases for the UPDATE target table
21
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
23
SELECT * FROM update_test;
25
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
27
SELECT * FROM update_test;
30
-- Test VALUES in FROM
33
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
34
WHERE update_test.b = v.j;
36
SELECT * FROM update_test;
39
-- Test multiple-set-clause syntax
42
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
43
SELECT * FROM update_test;
44
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
45
SELECT * FROM update_test;
46
-- fail, multi assignment to same column:
47
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
49
-- XXX this should work, but doesn't yet:
50
UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
53
-- if an alias for the target table is specified, don't allow references
54
-- to the original table name
56
SET LOCAL add_missing_from = false;
57
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
60
DROP TABLE update_test;