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: Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2)); Warnings: Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) 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 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;
Back to File Manager