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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
# name: test/sql/update/test_update_from.test
# description: Test updates that use a from clause
# group: [update]
# create a table
statement ok
CREATE TABLE test (a INTEGER);
statement ok
INSERT INTO test VALUES (3)
statement ok
CREATE TABLE src (a INTEGER);
statement ok
INSERT INTO src VALUES (2)
query I
SELECT * FROM test
----
3
query I
SELECT * FROM src
----
2
# test simple update
statement ok
UPDATE test SET a=test.a+s.a FROM src s
query I
SELECT * FROM test
----
5
# test self join via alias
statement ok
UPDATE test SET a=test.a+t.a FROM test t
query I
SELECT * FROM test
----
10
# test multiple tables
statement ok
UPDATE test SET a=t.a+s.a FROM test t, src s
query I
SELECT * FROM test
----
12
# test subquery
statement ok
UPDATE test SET a=s.q FROM (SELECT a+1 as q FROM src) s
query I
SELECT * FROM test
----
3
# test view
statement ok
CREATE VIEW vt AS (SELECT 17 as v)
statement ok
UPDATE test SET a=v FROM vt
query I
SELECT * FROM test
----
17
# with a where clause on the from table
statement ok
UPDATE test SET a=s.a FROM src s WHERE s.a = 2
query I
SELECT * FROM test
----
2
# with a where clause that involves both tables
statement ok
UPDATE test t SET a=1 FROM src s WHERE s.a = t.a
query I
SELECT * FROM test
----
1
# with a where clause that evaluates to false
statement ok
UPDATE test t SET a=9 FROM src s WHERE s.a=t.a
query I
SELECT * FROM test
----
1
# test with multiple updates per row (which is undefined),
# but in this case the last value in the table will win
# FIXME:
mode skip
statement ok
INSERT INTO src VALUES (7)
statement ok
UPDATE test SET a=s.a FROM src s
query I
SELECT * FROM test
----
7
mode unskip
# test described in issue 1035
statement ok
CREATE TABLE terms(docid INTEGER, term INTEGER);
statement ok
CREATE TABLE docs(id INTEGER, len INTEGER);
statement ok
insert into docs values (1, 0), (2, 0);
statement ok
insert into terms values (1, 1);
statement ok
insert into terms values (2, 1);
statement ok
insert into terms values (2, 2);
statement ok
insert into terms values (2, 3);
statement ok
UPDATE docs
SET len = sq.len
FROM (
SELECT docid AS id, count(term) AS len
FROM terms
GROUP BY docid
ORDER BY docid
) AS sq
WHERE docs.id = sq.id;
query II
select * from docs;
----
1 1
2 3
|
