blob: 55d82628e28d9dfe19b36eb2d4e55684c0caffea (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
--
-- UPDATE ... SET <col> = DEFAULT;
--
CREATE TABLE update_test (
a INT DEFAULT 10,
b INT
);
INSERT INTO update_test VALUES (5, 10);
INSERT INTO update_test VALUES (10, 15);
SELECT * FROM update_test;
a | b
----+----
5 | 10
10 | 15
(2 rows)
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
a | b
----+---
10 |
10 |
(2 rows)
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
a | b
----+----
10 | 10
10 | 10
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
a | b
----+----
10 | 20
10 | 20
(2 rows)
--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
SELECT * FROM update_test;
a | b
-----+----
100 | 20
100 | 20
(2 rows)
-- if an alias for the target table is specified, don't allow references
-- to the original table name
BEGIN;
SET LOCAL add_missing_from = false;
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ERROR: invalid reference to FROM-clause entry for table "update_test"
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
^
HINT: Perhaps you meant to reference the table alias "t".
ROLLBACK;
DROP TABLE update_test;
|
