DROP TABLE IF EXISTS t1,t2,t3,t4;
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 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 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 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 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 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);
SELECT * FROM t1;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	NULL
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2005	2005
2009	NULL
2061	NULL
2064	2064
2065	2065
2069	2069
2154	2154
2155	2155
SELECT * FROM t2;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	NULL
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2005	2005
2009	NULL
2061	NULL
2064	2064
2065	2065
2069	2069
2154	2154
2155	2155
SELECT * FROM t3;
c1	c2
00	00
01	01
01	01
02	02
04	04
05	05
09	NULL
54	54
55	55
61	NULL
64	64
65	65
69	69
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT * FROM t4;
c1	c2
00	00
01	01
01	01
02	02
04	04
05	05
09	NULL
54	54
55	55
61	NULL
64	64
65	65
69	69
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT c1 FROM t1 WHERE c1='2005';
c1
2005
UPDATE t1 SET c1='2003' WHERE c1='2005';
SELECT c1 FROM t1;
c1
1901
1902
1970
1971
1975
1979
1995
1996
1997
1998
1999
2000
2001
2003
2004
2009
2061
2064
2065
2069
2154
2155
SELECT c2 FROM t2 WHERE c2=null;
c2
UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996';
SELECT c2 FROM t2;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2154
2155
NULL
NULL
NULL
NULL
SELECT c2 FROM t3 WHERE c2 < '64';
c2
00
01
01
02
04
05
54
55
UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3;
SELECT c2 FROM t3;
c2
01
04
05
54
55
64
65
69
70
71
75
95
97
98
99
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT c1 FROM t4 WHERE c1 < '55';
c1
00
01
01
02
04
05
09
UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5;
SELECT c1 FROM t4;
c1
00
00
00
00
00
05
09
54
55
61
64
65
69
70
71
75
79
95
96
97
98
99
SELECT c1 FROM t3 WHERE c1='05';
c1
05
UPDATE t3 SET c1=c1+c2 WHERE c1='05';
SELECT c1 FROM t3;
c1
00
01
01
02
04
09
10
54
55
61
64
65
69
70
71
75
79
95
96
97
98
99
SELECT c1 FROM t4 WHERE c2=4;
c1
00
UPDATE t4 SET c1=c1 + 04 WHERE c2=04;
SELECT c1 FROM t4;
c1
00
00
00
00
04
05
09
54
55
61
64
65
69
70
71
75
79
95
96
97
98
99
SELECT c2 FROM t3 WHERE c2=75;
c2
75
UPDATE t3 SET c2=c2 + 1902 WHERE c2='75';
SELECT c2 FROM t3;
c2
01
04
05
54
55
64
65
69
70
71
77
95
97
98
99
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT c1 FROM t4 WHERE c1=09;
c1
09
UPDATE t4 SET c1=c1 - c2 WHERE c1='09';
SELECT c1 FROM t4;
c1
00
00
00
00
04
05
54
55
61
64
65
69
70
71
75
79
95
96
97
98
99
NULL
SELECT c1 FROM t3 WHERE c1=75;
c1
75
UPDATE t3 SET c1=c1 - 01 WHERE c1='75';
SELECT c1 FROM t3;
c1
00
01
01
02
04
09
10
54
55
61
64
65
69
70
71
74
79
95
96
97
98
99
SELECT c1 FROM t4 WHERE c1=98;
c1
98
UPDATE t4 SET c1=c1 / 2 WHERE c1='98';
SELECT c1 FROM t4;
c1
00
00
00
00
04
05
49
54
55
61
64
65
69
70
71
75
79
95
96
97
99
NULL
SELECT c2 FROM t3 WHERE c2=03;
c2
UPDATE t3 SET c2=c2 * 03 WHERE c2='03';
SELECT c2 FROM t3;
c2
01
04
05
54
55
64
65
69
70
71
77
95
97
98
99
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070;
c1
2061
2064
2065
2069
UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070;
SELECT c1 FROM t1;
c1
1901
1902
1970
1971
1975
1979
1995
1996
1997
1998
1999
2000
2001
2003
2004
2009
2071
2074
2075
2079
2154
2155
SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980;
c1
1970
1971
1975
1979
UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2;
SELECT c1 FROM t2;
c1
1901
1902
1960
1961
1975
1979
1995
1996
1997
1998
1999
2000
2001
2004
2005
2009
2061
2064
2065
2069
2154
2155
UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null;
SELECT c2 FROM t2;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2154
2155
NULL
NULL
NULL
NULL
UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154;
SELECT c2 FROM t1;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2155
2155
2155
NULL
NULL
NULL
UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null);
SELECT c1 FROM t4;
c1
00
00
04
05
49
55
61
64
70
71
75
79
95
96
97
99
99
99
99
99
99
NULL
UPDATE t1 SET c2=-1 WHERE c1=2071;
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
SELECT c2 FROM t1;
c2
0000
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2155
2155
2155
NULL
NULL
UPDATE t1 SET c2=0 WHERE c1=2003;
SELECT c2 FROM t1;
c2
0000
0000
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2064
2065
2069
2155
2155
2155
NULL
NULL
UPDATE t1 SET c2=2156 WHERE c1=2001;
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
SELECT c2 FROM t1;
c2
0000
0000
0000
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2004
2064
2065
2069
2155
2155
2155
NULL
NULL
UPDATE t4 SET c2=-1 WHERE c1=71;
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
SELECT c2 FROM t4;
c2
00
00
01
01
02
04
05
54
55
64
65
69
70
75
95
97
98
99
NULL
NULL
NULL
NULL
UPDATE t4 SET c2=0 WHERE c1=70;
SELECT c2 FROM t4;
c2
00
00
00
01
01
02
04
05
54
55
64
65
69
75
95
97
98
99
NULL
NULL
NULL
NULL
UPDATE t4 SET c2=100 WHERE c1=79;
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
SELECT c2 FROM t4;
c2
00
00
00
00
01
01
02
04
05
54
55
64
65
69
75
95
97
98
99
NULL
NULL
NULL
SELECT c1 FROM t1 WHERE c2 < 1975;
c1
1901
1902
1970
1971
2001
2003
2071
SET SQL_MODE=STRICT_ALL_TABLES;
UPDATE t1 SET c1=NULL WHERE c2 < 1975;
ERROR 23000: Column 'c1' cannot be null
UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975;
Warnings:
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
Warning	1048	Column 'c1' cannot be null
SELECT c1 from t1 WHERE c2 < 1975;
c1
0000
1902
1970
1971
2001
2003
2071
SET SQL_MODE=DEFAULT
#Update with invalid values
#UPDATE t2 SET c1='def' WHERE c2=2064;
UPDATE t4 SET c1=-70 WHERE c2=75;
Warnings:
Warning	1264	Out of range value for column 'c1' at row 17
SELECT c1 FROM t4;
c1
00
00
00
04
05
49
55
61
64
70
71
79
95
96
97
99
99
99
99
99
99
NULL
UPDATE t1 SET c1=1971 WHERE c2=1995;
ERROR 23000: Duplicate entry '1971' for key 'PRIMARY'
UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4;
UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064;
SELECT * FROM t1;
c1	c2
0000	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	2155
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	0000
2003	0000
2004	2004
2009	NULL
2071	0000
2073	2064
2075	2065
2079	2069
2154	2155
2155	2155
UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010;
SELECT * FROM t1;
c1	c2
0000	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	2155
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	0000
2001	0000
2003	0000
2004	0000
2009	0000
2071	0000
2073	2064
2075	2065
2079	2069
2154	2155
2155	2155
UPDATE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979);
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
Warning	1264	Out of range value for column 'c2' at row 3
SELECT * FROM t1;
c1	c2
0000	1901
1902	1902
1970	1970
1971	1971
1975	1975
1978	0000
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	0000
2001	0000
2003	0000
2004	0000
2008	2001
2071	0000
2073	2064
2075	2065
2079	2069
2153	0000
2155	2155
UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
SELECT * FROM t1;
SELECT * FROM t2;
c1	c2
1901	1901
1902	1902
1960	1970
1961	1971
1975	1975
1979	NULL
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2005	2005
2009	NULL
2061	NULL
2064	2064
2065	2065
2069	2069
2154	2154
2155	2155
SELECT * FROM t3;
c1	c2
00	NULL
01	01
01	NULL
02	NULL
04	04
08	NULL
09	NULL
10	05
54	54
55	55
64	64
65	65
69	69
70	70
71	71
74	77
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT * FROM t4;
c1	c2
00	00
00	02
00	75
04	04
05	05
49	98
55	55
61	NULL
64	64
70	00
71	00
79	00
95	95
96	NULL
97	97
99	01
99	01
99	54
99	65
99	69
99	99
NULL	NULL
DROP TABLE IF EXISTS t1,t2,t3,t4;