CREATE TABLE t5(c1 BIT(8) NOT NULL PRIMARY KEY, c2 BIT(8)); CREATE TABLE t6(c1 BIT(8), c2 BIT(8)); INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118, 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57, 135); INSERT INTO t6 VALUES (94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57, 135); --sorted_result SELECT HEX(c1),HEX(c2) FROM t5; --sorted_result SELECT HEX(c1),HEX(c2) FROM t6; --disable_warnings #Insert permissible NULLs INSERT IGNORE INTO t5 VALUES(96,null); INSERT INTO t6 VALUES(null,null); # Update order by limit UPDATE t6 SET c1='10' WHERE c2 > 100 ORDER BY c2 LIMIT 1; --sorted_result SELECT hex(c1),hex(c2) FROM t6; # Update with arithmetic operations UPDATE t6 SET c1=c2+c1 WHERE c2 < 100; --sorted_result SELECT hex(c1),hex(c2) FROM t6; UPDATE t6 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4; --sorted_result SELECT hex(c1),hex(c2) FROM t6; # Update with NULL ( NULL to number & number to NULL) UPDATE t6 SET c2=NULL WHERE c1=23; --sorted_result SELECT hex(c1),hex(c2) FROM t6; UPDATE t6 SET c2=10 WHERE c2=NULL; --sorted_result SELECT hex(c1),hex(c2) FROM t6; # Update range values UPDATE t6 SET c1=12 WHERE c1=b'101' AND c2=178; --sorted_result SELECT hex(c1),hex(c2) FROM t6; UPDATE t6 SET c1=c1+2,c2=c1+c2 WHERE c2=46 OR c2=b'1001'; --sorted_result SELECT hex(c1),hex(c2) FROM t6; UPDATE t6 SET c2=123 WHERE c1 IN (30,b'101',88); --sorted_result SELECT hex(c1),hex(c2) FROM t6; # Update outside range would be clipped to closest endpoints UPDATE t6 SET c1=b'1111111111111111111111111111111111111111111111111111111111111111111' WHERE c2 < 10; --sorted_result SELECT hex(c1),hex(c2) FROM t6; UPDATE t6 SET c2=-1 WHERE c1=94; --sorted_result SELECT hex(c1),hex(c2) FROM t6; # Update ignore on bad null error --sorted_result SELECT hex(c1) FROM t5 WHERE c1<>0; SET SQL_MODE=STRICT_ALL_TABLES; --error ER_BAD_NULL_ERROR UPDATE t5 SET c1=NULL WHERE c1<>0; UPDATE IGNORE t5 SET c1=NULL WHERE c1>100; --sorted_result SELECT hex(c1),hex(c2) FROM t5; SET SQL_MODE=DEFAULT; #Multi table update UPDATE t5,t6 SET t5.c2=t6.c1+t5.c1, t6.c2=t5.c1+t6.c1 WHERE t5.c1 > 100 OR t6.c2 > 100; --sorted_result SELECT hex(c1),hex(c2) FROM t5; --sorted_result SELECT hex(c1),hex(c2) FROM t6; DROP TABLE t5,t6;