1# Include to test update with same table as source and target
2
3--echo #
4--echo # Update a with value from subquery on the same table, no search clause. ALL access
5--echo #
6
7start transaction;
8--enable_info ONCE
9update t1
10   set c1=(select a.c3
11             from t1 a
12            where a.c3 = t1.c3);
13select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
14rollback;
15
16--echo #
17--echo # Update with search clause on the same table
18--echo #
19
20start transaction;
21--enable_info ONCE
22update t1
23   set c1=10
24 where c1 <2
25   and exists (select 'X'
26                 from t1 a
27                where a.c1 = t1.c1);
28select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
29rollback;
30
31--echo #
32--echo # Update via RANGE or INDEX access if an index or a primary key exists
33--echo #
34
35explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
36start transaction;
37--enable_info ONCE
38update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
39select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
40rollback;
41
42--echo #
43--echo # Update with order by
44--echo #
45
46start transaction;
47update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
48select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
49rollback;
50
51--echo #
52--echo Update using a view in subquery
53--echo #
54
55start transaction;
56--enable_info ONCE
57update t1
58   set c1=c1 +(select max(a.c2)
59             from v1 a
60            where a.c1 = t1.c1) ;
61select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
62rollback;
63
64--echo #
65--echo # Update throw a view
66--echo #
67
68start transaction;
69--enable_info ONCE
70update v1
71   set c1=c1 + (select max(a.c2)
72             from t1 a
73            where a.c1 = v1.c1) +10
74where c3 > 3;
75select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
76rollback;
77
78--echo #
79--echo # Update through a view and using the view in subquery
80--echo #
81
82start transaction;
83--enable_info ONCE
84update v1
85   set c1=c1 + 1
86 where c1 <2
87   and exists (select 'X'
88                 from v1 a
89                where a.c1 = v1.c1);
90select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
91rollback;
92
93--echo #
94--echo # Update through  a view and using the view in subquery
95--echo #
96
97start transaction;
98--enable_info ONCE
99update v1
100   set c1=(select max(a.c1)+10
101             from v1 a
102            where a.c1 = v1.c1)
103 where c1 <10
104   and exists (select 'X'
105                 from v1 a
106                where a.c2 = v1.c2);
107select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
108rollback;
109
110--echo #
111--echo # Update of the index or primary key (c3)
112--echo #
113
114start transaction;
115explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
116--enable_info ONCE
117update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
118select c3 from t1;
119rollback;
120
121--echo #
122--echo # update with a limit
123--echo #
124
125start transaction;
126--enable_info ONCE
127update t1
128   set c1=(select a.c3
129             from t1 a
130            where a.c3 = t1.c3)
131    limit 2;
132select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
133rollback;
134
135--echo #
136--echo # update with a limit and an order by
137--echo #
138
139start transaction;
140--enable_info ONCE
141update t1
142   set c1=(select a.c3
143             from t1 a
144            where a.c3 = t1.c3)
145  order by c3 desc limit 2;
146select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
147rollback;
148