Edit File: update_year.result
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)); Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2)); Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. 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 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 t4; 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 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 1901 1902 1970 1971 1975 1995 1997 1998 1999 2000 2001 2004 2005 UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3; SELECT c2 FROM t3; c2 1971 1975 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 NULL NULL NULL NULL NULL NULL NULL SELECT c1 FROM t4 WHERE c1 < '55'; c1 1901 1902 1970 1971 1975 1979 1995 1996 1997 1998 1999 2000 2001 2004 2005 2009 UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5; SELECT c1 FROM t4; c1 1979 1995 1996 1997 1998 1999 2000 2000 2000 2000 2000 2000 2001 2004 2005 2009 2061 2064 2065 2069 2154 2155 SELECT c1 FROM t3 WHERE c1='05'; c1 2005 UPDATE t3 SET c1=c1+c2 WHERE c1='05'; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT c1 FROM t3; c1 0000 1901 1902 1970 1971 1975 1979 1995 1996 1997 1998 1999 2000 2001 2004 2009 2061 2064 2065 2069 2154 2155 SELECT c1 FROM t4 WHERE c2=4; c1 2004 UPDATE t4 SET c1=c1 + 04 WHERE c2=04; SELECT c1 FROM t4; c1 1979 1995 1996 1997 1998 1999 2000 2000 2000 2000 2000 2000 2001 2005 2008 2009 2061 2064 2065 2069 2154 2155 SELECT c2 FROM t3 WHERE c2=75; c2 1975 UPDATE t3 SET c2=c2 + 1902 WHERE c2='75'; Warnings: Warning 1264 Out of range value for column 'c2' at row XX SELECT c2 FROM t3; c2 0000 1971 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 NULL NULL NULL NULL NULL NULL NULL SELECT c1 FROM t4 WHERE c1=09; c1 2009 UPDATE t4 SET c1=c1 - c2 WHERE c1='09'; SELECT c1 FROM t4; c1 1979 1995 1996 1997 1998 1999 2000 2000 2000 2000 2000 2000 2001 2005 2008 2061 2064 2065 2069 2154 2155 NULL SELECT c1 FROM t3 WHERE c1=75; c1 1975 UPDATE t3 SET c1=c1 - 01 WHERE c1='75'; SELECT c1 FROM t3; c1 0000 1901 1902 1970 1971 1974 1979 1995 1996 1997 1998 1999 2000 2001 2004 2009 2061 2064 2065 2069 2154 2155 SELECT c1 FROM t4 WHERE c1=98; c1 1998 UPDATE t4 SET c1=c1 / 2 WHERE c1='98'; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT c1 FROM t4; c1 0000 1979 1995 1996 1997 1999 2000 2000 2000 2000 2000 2000 2001 2005 2008 2061 2064 2065 2069 2154 2155 NULL SELECT c2 FROM t3 WHERE c2=03; c2 UPDATE t3 SET c2=c2 * 03 WHERE c2='03'; SELECT c2 FROM t3; c2 0000 1971 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 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 0000 1979 1995 1996 1997 1999 2000 2000 2000 2000 2000 2000 2001 2005 2008 2061 2064 2065 2069 2154 2155 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; SELECT c2 FROM t4; c2 1901 1902 1970 1971 1975 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 NULL NULL NULL NULL UPDATE t4 SET c2=0 WHERE c1=70; SELECT c2 FROM t4; c2 1901 1902 1970 1971 1975 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 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 0000 1901 1902 1970 1971 1975 1995 1997 1998 1999 2000 2001 2004 2005 2064 2065 2069 2154 2155 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 0000 0000 1979 1995 1996 1997 1999 2000 2000 2000 2000 2000 2001 2005 2008 2061 2064 2065 2069 2154 2155 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; ERROR 23000: Duplicate entry '0000' for key 'PRIMARY' 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 0000 2005 1901 NULL 1902 NULL 1970 NULL 1971 1971 1974 0000 1979 NULL 1995 1995 1996 NULL 1997 1997 1998 1998 1999 1999 2000 2000 2001 2001 2004 2004 2009 NULL 2061 NULL 2064 2064 2065 2065 2069 2069 2154 2154 2155 2155 SELECT * FROM t4; c1 c2 0000 1975 0000 1998 1979 0000 1995 1995 1996 NULL 1997 1997 1999 1999 2000 1901 2000 1902 2000 1970 2000 1971 2000 2000 2001 2001 2005 2005 2008 2004 2061 NULL 2064 2064 2065 2065 2069 2069 2154 2154 2155 2155 NULL NULL DROP TABLE IF EXISTS t1,t2,t3,t4;