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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
|
--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4;
--enable_warnings
# Create tables
CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
CREATE TABLE t3(c1 YEAR(2) NOT NULL,c2 YEAR(2), PRIMARY KEY(c1));
CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));
#Insert as 4-digit number format in the range '1901' to '2155'
INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
#Insert as 4-digit string format in the range '1901' to '2155'
INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
#Insert as 2-digit number format
INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
#Insert as 2-digit string format
INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
#Insert permissible NULLs
INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t2;
--sorted_result
SELECT * FROM t3;
--sorted_result
SELECT * FROM t4;
#Updating the tables
--sorted_result
SELECT c1 FROM t1 WHERE c1='2005';
UPDATE t1 SET c1='2003' WHERE c1='2005';
--sorted_result
SELECT c1 FROM t1;
# Update with NULL ( NULL to number & number to NULL)
--sorted_result
SELECT c2 FROM t2 WHERE c2=null;
UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996';
--sorted_result
SELECT c2 FROM t2;
--sorted_result
SELECT c2 FROM t3 WHERE c2 < '64';
UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3;
--sorted_result
SELECT c2 FROM t3;
#Update order by limit
--sorted_result
SELECT c1 FROM t4 WHERE c1 < '55';
UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5;
--sorted_result
SELECT c1 FROM t4;
#Update with Arithmetic operations
#
#Updating 'YY' values
--sorted_result
SELECT c1 FROM t3 WHERE c1='05';
UPDATE t3 SET c1=c1+c2 WHERE c1='05';
--sorted_result
SELECT c1 FROM t3;
--sorted_result
SELECT c1 FROM t4 WHERE c2=4;
UPDATE t4 SET c1=c1 + 04 WHERE c2=04;
--sorted_result
SELECT c1 FROM t4;
--sorted_result
SELECT c2 FROM t3 WHERE c2=75;
UPDATE t3 SET c2=c2 + 1902 WHERE c2='75';
--sorted_result
SELECT c2 FROM t3;
--sorted_result
SELECT c1 FROM t4 WHERE c1=09;
UPDATE t4 SET c1=c1 - c2 WHERE c1='09';
--sorted_result
SELECT c1 FROM t4;
--sorted_result
SELECT c1 FROM t3 WHERE c1=75;
UPDATE t3 SET c1=c1 - 01 WHERE c1='75';
--sorted_result
SELECT c1 FROM t3;
--sorted_result
SELECT c1 FROM t4 WHERE c1=98;
UPDATE t4 SET c1=c1 / 2 WHERE c1='98';
--sorted_result
SELECT c1 FROM t4;
--sorted_result
SELECT c2 FROM t3 WHERE c2=03;
UPDATE t3 SET c2=c2 * 03 WHERE c2='03';
--sorted_result
SELECT c2 FROM t3;
#
#Updating 'YYYY' values
--sorted_result
SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070;
UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070;
--sorted_result
SELECT c1 FROM t1;
--sorted_result
SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980;
UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2;
--sorted_result
SELECT c1 FROM t2;
#Update range values
UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null;
--sorted_result
SELECT c2 FROM t2;
UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154;
--sorted_result
SELECT c2 FROM t1;
UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null);
--sorted_result
SELECT c1 FROM t4;
# Update outside range would be clipped to closest endpoints
UPDATE IGNORE t1 SET c2=-1 WHERE c1=2071;
--sorted_result
SELECT c2 FROM t1;
UPDATE t1 SET c2=0 WHERE c1=2003;
--sorted_result
SELECT c2 FROM t1;
UPDATE IGNORE t1 SET c2=2156 WHERE c1=2001;
--sorted_result
SELECT c2 FROM t1;
UPDATE IGNORE t4 SET c2=-1 WHERE c1=71;
--sorted_result
SELECT c2 FROM t4;
UPDATE t4 SET c2=0 WHERE c1=70;
--sorted_result
SELECT c2 FROM t4;
UPDATE IGNORE t4 SET c2=100 WHERE c1=79;
--sorted_result
SELECT c2 FROM t4;
# Update ignore on bad null error
--sorted_result
SELECT c1 FROM t1 WHERE c2 < 1975;
SET SQL_MODE=STRICT_ALL_TABLES;
--error ER_BAD_NULL_ERROR
UPDATE t1 SET c1=NULL WHERE c2 < 1975;
UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975;
--sorted_result
SELECT c1 from t1 WHERE c2 < 1975;
SET SQL_MODE=DEFAULT
#Update with invalid values
#UPDATE t2 SET c1='def' WHERE c2=2064;
#--sorted_result
#SELECT c1 FROM t2;
UPDATE IGNORE t4 SET c1=-70 WHERE c2=75;
--sorted_result
SELECT c1 FROM t4;
#Update duplicate key
--error ER_DUP_ENTRY
UPDATE t1 SET c1=1971 WHERE c2=1995;
#Multi table update
UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4;
# Update using various access methods
# Update using Const
# EXPLAIN SELECT * FROM t1 WHERE c1=2074 AND c2=2064;
UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064;
--sorted_result
SELECT * FROM t1;
# Update using range
# EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 2000 AND 2010;
UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010;
--sorted_result
SELECT * FROM t1;
# EXPLAIN SELECT * FROM t1 WHERE c1 IN (2154,2009,1979);
UPDATE IGNORE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979);
--sorted_result
SELECT * FROM t1;
# Update using eq_ref
# EXPLAIN SELECT * FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
#BUG49910
--disable_result_log
--sorted_result
SELECT * FROM t1;
--enable_result_log
--sorted_result
SELECT * FROM t2;
--sorted_result
SELECT * FROM t3;
--sorted_result
SELECT * FROM t4;
#Drop tables
DROP TABLE IF EXISTS t1,t2,t3,t4;
|
