Linux ip-172-26-2-223 5.4.0-1018-aws #18-Ubuntu SMP Wed Jun 24 01:15:00 UTC 2020 x86_64
Apache
: 172.26.2.223 | : 3.134.253.192
Cant Read [ /etc/named.conf ]
8.1.13
www
www.github.com/MadExploits
Terminal
AUTO ROOT
Adminer
Backdoor Destroyer
Linux Exploit
Lock Shell
Lock File
Create User
CREATE RDP
PHP Mailer
BACKCONNECT
UNLOCK SHELL
HASH IDENTIFIER
CPANEL RESET
CREATE WP USER
BLACK DEFEND!
README
+ Create Folder
+ Create File
/
www /
server /
mysql /
mysql-test /
suite /
engines /
iuds /
t /
[ HOME SHELL ]
Name
Size
Permission
Action
delete_decimal.test
14.4
KB
-rw-r--r--
delete_time.test
6.46
KB
-rw-r--r--
delete_year.test
3.79
KB
-rw-r--r--
disabled.def
376
B
-rw-r--r--
hindi.txt
3.27
KB
-rw-r--r--
insert_calendar.test
334.57
KB
-rw-r--r--
insert_decimal.test
49.46
KB
-rw-r--r--
insert_number.test
384.36
KB
-rw-r--r--
insert_time.test
54.11
KB
-rw-r--r--
insert_year.test
73.64
KB
-rw-r--r--
sample.txt
11.23
KB
-rw-r--r--
strings_charsets_update_delete...
10.41
KB
-rw-r--r--
strings_update_delete.test
6.02
KB
-rw-r--r--
type_bit_iuds.test
759.19
KB
-rw-r--r--
update_decimal.test
19.15
KB
-rw-r--r--
update_delete_calendar.test
42.7
KB
-rw-r--r--
update_delete_number.test
73.53
KB
-rw-r--r--
update_time.test
8.61
KB
-rw-r--r--
update_year.test
6.49
KB
-rw-r--r--
Delete
Unzip
Zip
${this.title}
Close
Code Editor : update_decimal.test
--disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; --enable_warnings SET sql_mode='NO_ENGINE_SUBSTITUTION'; ######## Running UPDATE tests for DECIMAL ######## # Create tables CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c4)); # Inserting data into table t1 # Trailing zeroes are not stripped, D=5 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3); # c1, c2, c3 will be rounded automatically INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); # Inserting maximum values INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); # Test insert leading zero, +/- signs, overflow handling INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); INSERT INTO t1 VALUES ("-.1","-.1","-.1",8); INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); # Inserting in scientific notations INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); --sorted_result SELECT * FROM t1; # UPDATE starts here # Update order by limit UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1; --sorted_result SELECT c1,c2 FROM t1; # Update with arithmetic operations UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4; --sorted_result SELECT * FROM t1; # Update with NULL ( NULL to number & number to NULL) UPDATE t1 SET c2=NULL WHERE c4=4; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL; --sorted_result SELECT * FROM t1; # Update negative value to unsigned column UPDATE t1 SET c1=-1.0e+2 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.0e+20 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; # Update range values UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5; --sorted_result SELECT * FROM t1 WHERE c1=1.2e+2; UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6); --sorted_result SELECT * FROM t1; # Update outside range would be clipped to closest endpoints UPDATE t1 SET c1=99999.999999 WHERE c4=7; --sorted_result SELECT c1,c4 FROM t1; UPDATE t1 SET c2=-9999.0099 WHERE c4=5; --sorted_result SELECT c2,c4 FROM t1; # Update ignore on bad null error SELECT c1 FROM t1 WHERE c1>1000; SET SQL_MODE=STRICT_ALL_TABLES; --error ER_BAD_NULL_ERROR UPDATE t1 SET c1=NULL WHERE c1>1000; UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000; --sorted_result SELECT c1 from t1 WHERE c1>1000; SET SQL_MODE='NO_ENGINE_SUBSTITUTION'; TRUNCATE t1; INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); --sorted_result SELECT * FROM t1; SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; --sorted_result SELECT * FROM t1; # Multi table update CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3)); CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3)); CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t1; # Update using various access methods TRUNCATE t2; TRUNCATE t3; INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); # Update using Const # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2; --sorted_result SELECT * FROM t2; # Update using range # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444'; --sorted_result SELECT * FROM t2; # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999'); --sorted_result SELECT * FROM t2; # Update using eq_ref DROP TABLE t2,t3; CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT); CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; #Updating with invalid values UPDATE t1 SET c3='asdf' WHERE c1='11111.11111'; --sorted_result SELECT c3 FROM t1; UPDATE t1 SET c2="1 e 1" WHERE c4=2; --sorted_result SELECT c2 FROM t1; #Duplicate keys --error ER_DUP_ENTRY UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Droping the tables --disable_warnings DROP TABLES IF EXISTS t1,t2,t3,t4; --enable_warnings ######## Running UPDATE tests for FLOAT ######## # Create tables CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c4)); # Inserting data into table t1 # Trailing zeroes are not stripped, D=5 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3); # c1, c2, c3 will be rounded automatically INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); # Inserting maximum values INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); # Test insert leading zero, +/- signs, overflow handling INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); INSERT INTO t1 VALUES ("-.1","-.1","-.1",8); INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); # Inserting in scientific notations INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); --sorted_result SELECT * FROM t1; # UPDATE starts here # Update order by limit UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1; --sorted_result SELECT c1,c2 FROM t1; # Update with arithmetic operations UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4; --sorted_result SELECT * FROM t1; # Update with NULL ( NULL to number & number to NULL) UPDATE t1 SET c2=NULL WHERE c4=4; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL; --sorted_result SELECT * FROM t1; # Update negative value to unsigned column UPDATE t1 SET c1=-1.0e+2 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.0e+20 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; # Update range values UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5; --sorted_result SELECT * FROM t1 WHERE c1=1.2e+2; UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6); --sorted_result SELECT * FROM t1; # Update outside range would be clipped to closest endpoints UPDATE t1 SET c1=99999.999999 WHERE c4=7; --sorted_result SELECT c1,c4 FROM t1; UPDATE t1 SET c2=-9999.0099 WHERE c4=5; --sorted_result SELECT c2,c4 FROM t1; # Update ignore on bad null error SELECT c1 FROM t1 WHERE c1>1000; SET SQL_MODE=STRICT_ALL_TABLES; --error ER_BAD_NULL_ERROR UPDATE t1 SET c1=NULL WHERE c1>1000; UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000; --sorted_result SELECT c1 from t1 WHERE c1>1000; SET SQL_MODE='NO_ENGINE_SUBSTITUTION'; TRUNCATE t1; INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; # Multi table update CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3)); CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3)); CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t4; UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; # Update using various access methods TRUNCATE t2; TRUNCATE t3; INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); # Update using Const # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2; --sorted_result SELECT * FROM t2; # Update using range # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444'; --sorted_result SELECT * FROM t2; # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999'); --sorted_result SELECT * FROM t2; # Update using eq_ref DROP TABLE t2,t3; CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT); CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; #Updating with invalid values UPDATE t1 SET c3='asdf' WHERE c1='11111.11111'; --sorted_result SELECT c3 FROM t1; UPDATE t1 SET c2="1 e 1" WHERE c4=2; --replace_result 88888.89063 88888.89062 --sorted_result SELECT c2 FROM t1; #Duplicate keys --replace_result 88888.89063 88888.89062 --error ER_DUP_ENTRY UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --replace_result 88888.89063 88888.89062 --sorted_result SELECT * FROM t4; # Droping the tables --disable_warnings DROP TABLES IF EXISTS t1,t2,t3,t4; --enable_warnings ######## Running UPDATE tests for DOUBLE ######## # Create tables CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c4)); # Inserting data into table t1 # Trailing zeroes are not stripped, D=5 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3); # c1, c2, c3 will be rounded automatically INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); # Inserting maximum values INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); # Test insert leading zero, +/- signs, overflow handling INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); INSERT INTO t1 VALUES ("-.1","-.1","-.1",8); INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); # Inserting in scientific notations INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); --sorted_result SELECT * FROM t1; # UPDATE starts here # Update order by limit UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1; --sorted_result SELECT c1,c2 FROM t1; # Update with arithmetic operations UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4; --sorted_result SELECT * FROM t1; # Update with NULL ( NULL to number & number to NULL) UPDATE t1 SET c2=NULL WHERE c4=4; --sorted_result SELECT * FROM t1; UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL; --sorted_result SELECT * FROM t1; # Update negative value to unsigned column UPDATE t1 SET c1=-1.0e+2 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.0e+20 WHERE c4=2; SHOW WARNINGS; --sorted_result SELECT * FROM t1; # Update range values UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7; --sorted_result SELECT * FROM t1; UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5; --sorted_result SELECT * FROM t1 WHERE c1=1.2e+2; UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6); --sorted_result SELECT * FROM t1; # Update outside range would be clipped to closest endpoints UPDATE t1 SET c1=99999.999999 WHERE c4=7; --sorted_result SELECT c1,c4 FROM t1; UPDATE t1 SET c2=-9999.0099 WHERE c4=5; --sorted_result SELECT c2,c4 FROM t1; # Update ignore on bad null error SELECT c1 FROM t1 WHERE c1>1000; SET SQL_MODE=STRICT_ALL_TABLES; --error ER_BAD_NULL_ERROR UPDATE t1 SET c1=NULL WHERE c1>1000; UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000; --sorted_result SELECT c1 from t1 WHERE c1>1000; SET SQL_MODE='NO_ENGINE_SUBSTITUTION'; TRUNCATE t1; INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); --sorted_result SELECT * FROM t1; SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; --sorted_result SELECT * FROM t1; # Multi table update CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3)); CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3)); CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t1; # Update using various access methods TRUNCATE t2; TRUNCATE t3; INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); # Update using Const # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2; --sorted_result SELECT * FROM t2; # Update using range # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444'; --sorted_result SELECT * FROM t2; # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999'); --sorted_result SELECT * FROM t2; # Update using eq_ref DROP TABLE t2,t3; CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT); CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1)); INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; #Updating with invalid values UPDATE t1 SET c3='asdf' WHERE c1='11111.11111'; --sorted_result SELECT c3 FROM t1; UPDATE t1 SET c2="1 e 1" WHERE c4=2; --sorted_result SELECT c2 FROM t1; #Duplicate keys --error ER_DUP_ENTRY UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Droping the tables --disable_warnings DROP TABLES IF EXISTS t1,t2,t3,t4; --enable_warnings SET sql_mode=default;
Close
