958 lines
27 KiB
Plaintext
958 lines
27 KiB
Plaintext
drop table if exists t;
|
|
create table t(a bigint, b bigint as (a+1));
|
|
begin;
|
|
insert into t(a) values(1);
|
|
update t set b=6 where b=2;
|
|
Error 3105 (HY000): The value specified for generated column 'b' in table 't' is not allowed.
|
|
commit;
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
drop table if exists t1, t2, t3;
|
|
create table t1(id int primary key auto_increment, n int);
|
|
create table t2(id int primary key, n float auto_increment, key I_n(n));
|
|
create table t3(id int primary key, n double auto_increment, key I_n(n));
|
|
insert into t1 set n = 1;
|
|
select * from t1 where id = 1;
|
|
id n
|
|
1 1
|
|
update t1 set id = id+1;
|
|
select * from t1 where id = 2;
|
|
id n
|
|
2 1
|
|
insert into t1 set n = 2;
|
|
select * from t1 where id = 3;
|
|
id n
|
|
3 2
|
|
update t1 set id = id + '1.1' where id = 3;
|
|
select * from t1 where id = 4;
|
|
id n
|
|
4 2
|
|
insert into t1 set n = 3;
|
|
select * from t1 where id = 5;
|
|
id n
|
|
5 3
|
|
update t1 set id = id + '0.5' where id = 5;
|
|
select * from t1 where id = 6;
|
|
id n
|
|
6 3
|
|
insert into t1 set n = 4;
|
|
select * from t1 where id = 7;
|
|
id n
|
|
7 4
|
|
insert into t2 set id = 1;
|
|
select * from t2 where id = 1;
|
|
id n
|
|
1 1
|
|
update t2 set n = n+1;
|
|
select * from t2 where id = 1;
|
|
id n
|
|
1 2
|
|
insert into t2 set id = 2;
|
|
select * from t2 where id = 2;
|
|
id n
|
|
2 3
|
|
update t2 set n = n + '2.2';
|
|
select * from t2 where id = 2;
|
|
id n
|
|
2 5.2
|
|
insert into t2 set id = 3;
|
|
select * from t2 where id = 3;
|
|
id n
|
|
3 6
|
|
update t2 set n = n + '0.5' where id = 3;
|
|
select * from t2 where id = 3;
|
|
id n
|
|
3 6.5
|
|
insert into t2 set id = 4;
|
|
select * from t2 where id = 4;
|
|
id n
|
|
4 7
|
|
insert into t3 set id = 1;
|
|
select * from t3 where id = 1;
|
|
id n
|
|
1 1
|
|
update t3 set n = n+1;
|
|
select * from t3 where id = 1;
|
|
id n
|
|
1 2
|
|
insert into t3 set id = 2;
|
|
select * from t3 where id = 2;
|
|
id n
|
|
2 3
|
|
update t3 set n = n + '3.3';
|
|
select * from t3 where id = 2;
|
|
id n
|
|
2 6.3
|
|
insert into t3 set id = 3;
|
|
select * from t3 where id = 3;
|
|
id n
|
|
3 7
|
|
update t3 set n = n + '0.5' where id = 3;
|
|
select * from t3 where id = 3;
|
|
id n
|
|
3 7.5
|
|
insert into t3 set id = 4;
|
|
select * from t3 where id = 4;
|
|
id n
|
|
4 8
|
|
drop table if exists t;
|
|
drop database if exists test2;
|
|
create database test2;
|
|
create table t(a int, b int generated always as (a+1) virtual);
|
|
create table test2.t(a int, b int generated always as (a+1) virtual);
|
|
update t, test2.t set executor__update.t.a=1;
|
|
drop database test2;
|
|
drop table if exists t1, t2;
|
|
create table t1 (c_str varchar(40));
|
|
create table t2 (c_str varchar(40));
|
|
insert into t1 values ('Alice');
|
|
insert into t2 values ('Bob');
|
|
select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
|
|
c_str c_str
|
|
Alice Bob
|
|
update t1, t2 set t1.c_str = t2.c_str, t2.c_str = t1.c_str where t1.c_str <= t2.c_str;
|
|
select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
|
|
c_str c_str
|
|
drop table if exists t;
|
|
create table t (a int, b int);
|
|
insert into t values(1, 2);
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
update t set a=b, b=a;
|
|
select * from t;
|
|
a b
|
|
2 1
|
|
drop table if exists t;
|
|
create table t (a int, b int);
|
|
insert into t values (1,3);
|
|
select * from t;
|
|
a b
|
|
1 3
|
|
update t set a=b, b=a;
|
|
select * from t;
|
|
a b
|
|
3 1
|
|
drop table if exists t;
|
|
create table t (a int, b int, c int as (-a) virtual, d int as (-b) stored);
|
|
insert into t(a, b) values (10, 11), (20, 22);
|
|
select * from t;
|
|
a b c d
|
|
10 11 -10 -11
|
|
20 22 -20 -22
|
|
update t set a=b, b=a;
|
|
select * from t;
|
|
a b c d
|
|
11 10 -11 -10
|
|
22 20 -22 -20
|
|
update t set b=30, a=b;
|
|
select * from t;
|
|
a b c d
|
|
10 30 -10 -30
|
|
20 30 -20 -30
|
|
drop table if exists t;
|
|
create table t(x int, y int);
|
|
insert into t values();
|
|
update t t1, t t2 set t2.y=1, t1.x=2;
|
|
select * from t;
|
|
x y
|
|
2 1
|
|
update t t1, t t2 set t1.x=t2.y, t2.y=t1.x;
|
|
select * from t;
|
|
x y
|
|
1 2
|
|
drop table if exists t;
|
|
create table t(x int, y int, z int as (x+10) stored, w int as (y-10) virtual);
|
|
insert into t(x, y) values(1, 2), (3, 4);
|
|
update t t1, t t2 set t2.y=1, t1.x=2 where t1.x=1;
|
|
select * from t;
|
|
x y z w
|
|
2 1 12 -9
|
|
3 1 13 -9
|
|
update t t1, t t2 set t1.x=5, t2.y=t1.x where t1.x=3;
|
|
select * from t;
|
|
x y z w
|
|
2 3 12 -7
|
|
5 3 15 -7
|
|
drop table if exists t;
|
|
create table t(a int, b int, c int as (a+b) stored);
|
|
insert into t(a, b) values (1, 2);
|
|
update t t1, t t2 set t2.a=3;
|
|
select * from t;
|
|
a b c
|
|
3 2 5
|
|
update t t1, t t2 set t1.a=4, t2.b=5;
|
|
select * from t;
|
|
a b c
|
|
4 5 9
|
|
drop table if exists t;
|
|
create table t (a int primary key);
|
|
insert into t values (1), (2);
|
|
update t set a=a+2;
|
|
select * from t;
|
|
a
|
|
3
|
|
4
|
|
update t m, t n set m.a = n.a+10 where m.a=n.a;
|
|
select * from t;
|
|
a
|
|
13
|
|
14
|
|
drop table if exists t;
|
|
create table t (a int primary key, b int);
|
|
insert into t values (1,3), (2,4);
|
|
update t m, t n set m.a = n.a+10, n.b = m.b+1 where m.a=n.a;
|
|
Error 1706 (HY000): Primary key/partition key update is not allowed since the table is updated both as 'm' and 'n'.
|
|
drop table if exists t;
|
|
create table t (a int, b int, c int, primary key(a, b));
|
|
insert into t values (1,3,5), (2,4,6);
|
|
update t m, t n set m.a = n.a+10, m.b = n.b+10 where m.a=n.a;
|
|
select * from t;
|
|
a b c
|
|
11 13 5
|
|
12 14 6
|
|
update t m, t n, t q set q.c=m.a+n.b, n.c = m.a+1, m.c = n.b+1 where m.b=n.b AND m.a=q.a;
|
|
select * from t;
|
|
a b c
|
|
11 13 24
|
|
12 14 26
|
|
update t m, t n, t q set m.a = m.a+1, n.c = n.c-1, q.c = q.a+q.b where m.b=n.b and n.b=q.b;
|
|
Error 1706 (HY000): Primary key/partition key update is not allowed since the table is updated both as 'm' and 'n'.
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists t;
|
|
create table t(id varchar(200) primary key, v int);
|
|
insert into t(id, v) values ('abc', 233);
|
|
select id, v from t where id = 'abc';
|
|
id v
|
|
abc 233
|
|
update t set id = 'dfg' where id = 'abc';
|
|
select * from t;
|
|
id v
|
|
dfg 233
|
|
update t set id = 'aaa', v = 333 where id = 'dfg';
|
|
select * from t where id = 'aaa';
|
|
id v
|
|
aaa 333
|
|
update t set v = 222 where id = 'aaa';
|
|
select * from t where id = 'aaa';
|
|
id v
|
|
aaa 222
|
|
insert into t(id, v) values ('bbb', 111);
|
|
update t set id = 'bbb' where id = 'aaa';
|
|
Error 1062 (23000): Duplicate entry 'bbb' for key 't.PRIMARY'
|
|
drop table if exists ut3pk;
|
|
create table ut3pk(id1 varchar(200), id2 varchar(200), v int, id3 int, primary key(id1, id2, id3));
|
|
insert into ut3pk(id1, id2, v, id3) values ('aaa', 'bbb', 233, 111);
|
|
select id1, id2, id3, v from ut3pk where id1 = 'aaa' and id2 = 'bbb' and id3 = 111;
|
|
id1 id2 id3 v
|
|
aaa bbb 111 233
|
|
update ut3pk set id1 = 'abc', id2 = 'bbb2', id3 = 222, v = 555 where id1 = 'aaa' and id2 = 'bbb' and id3 = 111;
|
|
select id1, id2, id3, v from ut3pk where id1 = 'abc' and id2 = 'bbb2' and id3 = 222;
|
|
id1 id2 id3 v
|
|
abc bbb2 222 555
|
|
select id1, id2, id3, v from ut3pk;
|
|
id1 id2 id3 v
|
|
abc bbb2 222 555
|
|
update ut3pk set v = 666 where id1 = 'abc' and id2 = 'bbb2' and id3 = 222;
|
|
select id1, id2, id3, v from ut3pk;
|
|
id1 id2 id3 v
|
|
abc bbb2 222 666
|
|
insert into ut3pk(id1, id2, id3, v) values ('abc', 'bbb3', 222, 777);
|
|
update ut3pk set id2 = 'bbb3' where id1 = 'abc' and id2 = 'bbb2' and id3 = 222;
|
|
Error 1062 (23000): Duplicate entry 'abc-bbb3-222' for key 'ut3pk.PRIMARY'
|
|
drop table if exists ut1pku;
|
|
create table ut1pku(id varchar(200) primary key, uk int, v int, unique key ukk(uk));
|
|
insert into ut1pku(id, uk, v) values('a', 1, 2), ('b', 2, 3);
|
|
select * from ut1pku;
|
|
id uk v
|
|
a 1 2
|
|
b 2 3
|
|
update ut1pku set uk = 3 where id = 'a';
|
|
select * from ut1pku;
|
|
id uk v
|
|
a 3 2
|
|
b 2 3
|
|
update ut1pku set uk = 2 where id = 'a';
|
|
Error 1062 (23000): Duplicate entry '2' for key 'ut1pku.ukk'
|
|
select * from ut1pku;
|
|
id uk v
|
|
a 3 2
|
|
b 2 3
|
|
drop table if exists t;
|
|
create table t(a char(10) primary key, b char(10));
|
|
insert into t values('a', 'b');
|
|
update t set a='c' where t.a='a' and b='b';
|
|
select * from t;
|
|
a b
|
|
c b
|
|
drop table if exists s;
|
|
create table s (a int, b int, c int, primary key (a, b));
|
|
insert s values (3, 3, 3), (5, 5, 5);
|
|
update s set c = 10 where a = 3;
|
|
select * from s;
|
|
a b c
|
|
3 3 10
|
|
5 5 5
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists t;
|
|
create table t(id varchar(200) primary key, v int);
|
|
insert into t(id, v) values ('abc', 233);
|
|
delete from t where id = 'abc';
|
|
select * from t;
|
|
id v
|
|
select * from t where id = 'abc';
|
|
id v
|
|
drop table if exists it3pk;
|
|
create table it3pk(id1 varchar(200), id2 varchar(200), v int, id3 int, primary key(id1, id2, id3));
|
|
insert into it3pk(id1, id2, v, id3) values ('aaa', 'bbb', 233, 111);
|
|
delete from it3pk where id1 = 'aaa' and id2 = 'bbb' and id3 = 111;
|
|
select * from it3pk;
|
|
id1 id2 v id3
|
|
select * from it3pk where id1 = 'aaa' and id2 = 'bbb' and id3 = 111;
|
|
id1 id2 v id3
|
|
insert into it3pk(id1, id2, v, id3) values ('aaa', 'bbb', 433, 111);
|
|
select * from it3pk where id1 = 'aaa' and id2 = 'bbb' and id3 = 111;
|
|
id1 id2 v id3
|
|
aaa bbb 433 111
|
|
drop table if exists dt3pku;
|
|
create table dt3pku(id varchar(200) primary key, uk int, v int, unique key uuk(uk));
|
|
insert into dt3pku(id, uk, v) values('a', 1, 2);
|
|
delete from dt3pku where id = 'a';
|
|
select * from dt3pku;
|
|
id uk v
|
|
insert into dt3pku(id, uk, v) values('a', 1, 2);
|
|
drop table if exists s1;
|
|
create table s1 (a int, b int, c int, primary key (a, b));
|
|
insert s1 values (3, 3, 3), (5, 5, 5);
|
|
delete from s1 where a = 3;
|
|
select * from s1;
|
|
a b c
|
|
5 5 5
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists rt1pk;
|
|
create table rt1pk(id varchar(200) primary key, v int);
|
|
replace into rt1pk(id, v) values('abc', 1);
|
|
select * from rt1pk;
|
|
id v
|
|
abc 1
|
|
replace into rt1pk(id, v) values('bbb', 233), ('abc', 2);
|
|
select * from rt1pk;
|
|
id v
|
|
abc 2
|
|
bbb 233
|
|
drop table if exists rt3pk;
|
|
create table rt3pk(id1 timestamp, id2 time, v int, id3 year, primary key(id1, id2, id3));
|
|
replace into rt3pk(id1, id2,id3, v) values('2018-01-01 11:11:11', '22:22:22', '2019', 1);
|
|
select * from rt3pk;
|
|
id1 id2 v id3
|
|
2018-01-01 11:11:11 22:22:22 1 2019
|
|
replace into rt3pk(id1, id2, id3, v) values('2018-01-01 11:11:11', '22:22:22', '2019', 2);
|
|
select * from rt3pk;
|
|
id1 id2 v id3
|
|
2018-01-01 11:11:11 22:22:22 2 2019
|
|
drop table if exists rt1pk1u;
|
|
create table rt1pk1u(id varchar(200) primary key, uk int, v int, unique key uuk(uk));
|
|
replace into rt1pk1u(id, uk, v) values("abc", 2, 1);
|
|
select * from rt1pk1u;
|
|
id uk v
|
|
abc 2 1
|
|
replace into rt1pk1u(id, uk, v) values("aaa", 2, 11);
|
|
select * from rt1pk1u;
|
|
id uk v
|
|
aaa 2 11
|
|
set tidb_enable_clustered_index = default;
|
|
drop table if exists t;
|
|
create table t(ts int(10) unsigned NULL DEFAULT NULL);
|
|
insert into t values(1);
|
|
update t set ts = IF(ts < (0 - ts), 1,1) where ts>0;
|
|
Error 1690 (22003): BIGINT UNSIGNED value is out of range in '(0 - executor__update.t.ts)'
|
|
drop table if exists tt;
|
|
create table tt (m0 varchar(64), status tinyint not null);
|
|
insert into tt values('1',0),('1',0),('1',0);
|
|
update tt a inner join (select m0 from tt where status!=1 group by m0 having count(*)>1) b on a.m0=b.m0 set a.status=1;
|
|
drop table if exists t1;
|
|
create table t1(id int, a int unsigned);
|
|
set sql_mode='';
|
|
insert into t1 values(1, 10), (2, 20);
|
|
update t1 set a='-1' where id=1;
|
|
update t1 set a='1000000000000000000' where id=2;
|
|
select id, a from t1 order by id asc;
|
|
id a
|
|
1 0
|
|
2 4294967295
|
|
set sql_mode=default;
|
|
drop table if exists t1;
|
|
create table t1(id int primary key, name varchar(40));
|
|
insert into t1 values(1, 'abc');
|
|
begin pessimistic;
|
|
begin pessimistic;
|
|
update t1 set name='xyz' where id=1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
select * from t1 where id = 1;
|
|
id name
|
|
1 xyz
|
|
commit;
|
|
update t1 set name='xyz' where id=1;
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 0
|
|
select * from t1 where id = 1;
|
|
id name
|
|
1 abc
|
|
select * from t1 where id = 1 for update;
|
|
id name
|
|
1 xyz
|
|
select * from t1 where id in (1, 2);
|
|
id name
|
|
1 abc
|
|
select * from t1 where id in (1, 2) for update;
|
|
id name
|
|
1 xyz
|
|
commit;
|
|
drop table if exists update_test;
|
|
create table update_test(id int not null default 1, name varchar(255), PRIMARY KEY(id));
|
|
insert INTO update_test VALUES (1, "hello");
|
|
insert into update_test values (2, "hello");
|
|
UPDATE update_test SET name = "abc" where id > 0;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
begin;
|
|
SELECT * from update_test limit 2;
|
|
id name
|
|
1 abc
|
|
2 abc
|
|
commit;
|
|
UPDATE update_test SET name = "foo";
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
begin;
|
|
drop table if exists update_test;
|
|
commit;
|
|
begin;
|
|
create table update_test(id int not null auto_increment, name varchar(255), primary key(id));
|
|
insert into update_test(name) values ('aa');
|
|
update update_test set id = 8 where name = 'aa';
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
insert into update_test(name) values ('bb');
|
|
commit;
|
|
begin;
|
|
select * from update_test;
|
|
id name
|
|
8 aa
|
|
9 bb
|
|
commit;
|
|
begin;
|
|
drop table if exists update_test;
|
|
commit;
|
|
begin;
|
|
create table update_test(id int not null auto_increment, name varchar(255), index(id));
|
|
insert into update_test(name) values ('aa');
|
|
update update_test set id = null where name = 'aa';
|
|
Error 1048 (23000): Column 'id' cannot be null
|
|
drop table update_test;
|
|
create table update_test(id int);
|
|
begin;
|
|
insert into update_test(id) values (1);
|
|
update update_test set id = 2 where id = 1 limit 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
select * from update_test;
|
|
id
|
|
2
|
|
commit;
|
|
drop table if exists update_unique;
|
|
create table update_unique (id int primary key, name int unique);
|
|
insert update_unique values (1, 1), (2, 2);
|
|
begin;
|
|
update update_unique set name = 1 where id = 2;
|
|
Error 1062 (23000): Duplicate entry '1' for key 'update_unique.name'
|
|
commit;
|
|
select * from update_unique;
|
|
id name
|
|
1 1
|
|
2 2
|
|
drop table if exists t;
|
|
create table t(a bigint, primary key (a));
|
|
insert into t values (1);
|
|
insert into t values (2);
|
|
update ignore t set a = 1 where a = 2;
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 1
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '1' for key 't.PRIMARY'
|
|
select * from t;
|
|
a
|
|
1
|
|
2
|
|
update ignore t set a = 1 where a = (select '2a');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DOUBLE value: '2a'
|
|
Warning 1292 Truncated incorrect DOUBLE value: '2a'
|
|
Warning 1062 Duplicate entry '1' for key 't.PRIMARY'
|
|
update ignore t set a = 42 where a = 2;
|
|
select * from t;
|
|
a
|
|
1
|
|
42
|
|
drop table if exists t;
|
|
create table t(a bigint, unique key I_uniq (a));
|
|
insert into t values (1);
|
|
insert into t values (2);
|
|
update ignore t set a = 1 where a = 2;
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 1
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '1' for key 't.I_uniq'
|
|
select * from t;
|
|
a
|
|
1
|
|
2
|
|
drop table if exists t;
|
|
create table t (a int) partition by list (a) (partition p0 values in (0,1));
|
|
analyze table t;
|
|
insert ignore into t values (1);
|
|
update ignore t set a=2 where a=1;
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 1
|
|
drop table if exists t;
|
|
create table t (a int key) partition by list (a) (partition p0 values in (0,1));
|
|
insert ignore into t values (1);
|
|
update ignore t set a=2 where a=1;
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 1
|
|
drop table if exists t;
|
|
create table t(id integer auto_increment, t1 datetime, t2 datetime, primary key (id));
|
|
insert into t(t1, t2) values('2000-10-01 01:01:01', '2017-01-01 10:10:10');
|
|
select * from t;
|
|
id t1 t2
|
|
1 2000-10-01 01:01:01 2017-01-01 10:10:10
|
|
update t set t1 = '2017-10-01 10:10:11', t2 = date_add(t1, INTERVAL 10 MINUTE) where id = 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
select * from t;
|
|
id t1 t2
|
|
1 2017-10-01 10:10:11 2000-10-01 01:11:01
|
|
drop table if exists tt1;
|
|
CREATE TABLE `tt1` (`a` int(11) NOT NULL,`b` varchar(32) DEFAULT NULL,`c` varchar(32) DEFAULT NULL,PRIMARY KEY (`a`),UNIQUE KEY `b_idx` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
insert into tt1 values(1, 'a', 'a');
|
|
insert into tt1 values(2, 'd', 'b');
|
|
select * from tt1;
|
|
a b c
|
|
1 a a
|
|
2 d b
|
|
update tt1 set a=5 where c='b';
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
select * from tt1;
|
|
a b c
|
|
1 a a
|
|
5 d b
|
|
drop table if exists tsup;
|
|
CREATE TABLE `tsup` (`a` int,`ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,KEY `idx` (`ts`));
|
|
set @@sql_mode='';
|
|
insert into tsup values(1, '0000-00-00 00:00:00');
|
|
update tsup set a=5;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
select t1.ts = t2.ts from (select ts from tsup use index (idx)) as t1, (select ts from tsup use index ()) as t2;
|
|
t1.ts = t2.ts
|
|
1
|
|
update tsup set ts='2019-01-01';
|
|
select ts from tsup;
|
|
ts
|
|
2019-01-01 00:00:00
|
|
set @@sql_mode=default;
|
|
drop table if exists decimals;
|
|
create table decimals (a decimal(20, 0) not null);
|
|
insert into decimals values (201);
|
|
update decimals set a = a + 1.23;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 1
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DECIMAL value: '202.23'
|
|
select * from decimals;
|
|
a
|
|
202
|
|
drop table t;
|
|
CREATE TABLE `t` ( `c1` year DEFAULT NULL, `c2` year DEFAULT NULL, `c3` date DEFAULT NULL, `c4` datetime DEFAULT NULL, KEY `idx` (`c1`,`c2`));
|
|
UPDATE t SET c2=16777215 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1 LIMIT 2;
|
|
update (select * from t) t set c1 = 1111111;
|
|
Error 1288 (HY000): The target table t of the UPDATE is not updatable
|
|
drop table if exists t;
|
|
create table t (i int not null default 10);
|
|
insert into t values (1);
|
|
update ignore t set i = null;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 1
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1048 Column 'i' cannot be null
|
|
select * from t;
|
|
i
|
|
0
|
|
drop table t;
|
|
create table t (k int, v int);
|
|
update t, (select * from t) as b set b.k = t.k;
|
|
Error 1288 (HY000): The target table b of the UPDATE is not updatable
|
|
update t, (select * from t) as b set t.k = b.k;
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (c1 float);
|
|
INSERT INTO t1 SET c1 = 1;
|
|
UPDATE t1 SET c1 = 1.2 WHERE c1=1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
drop table if exists t;
|
|
create table t (c1 float(1,1));
|
|
insert into t values (0.0);
|
|
update t set c1 = 2.0;
|
|
Error 1264 (22003): Out of range value for column 'c1' at row 1
|
|
drop table if exists t;
|
|
create table t(a datetime not null, b datetime);
|
|
insert into t value('1999-12-12', '1999-12-13');
|
|
set @@sql_mode='';
|
|
select * from t;
|
|
a b
|
|
1999-12-12 00:00:00 1999-12-13 00:00:00
|
|
update t set a = '';
|
|
select * from t;
|
|
a b
|
|
0000-00-00 00:00:00 1999-12-13 00:00:00
|
|
update t set b = '';
|
|
select * from t;
|
|
a b
|
|
0000-00-00 00:00:00 0000-00-00 00:00:00
|
|
set @@sql_mode=default;
|
|
drop view if exists v;
|
|
create view v as select * from t;
|
|
update v set a = '2000-11-11';
|
|
Error 1288 (HY000): The target table v of the UPDATE is not updatable
|
|
drop view v;
|
|
drop sequence if exists seq;
|
|
create sequence seq;
|
|
update seq set minvalue=1;
|
|
Error 1054 (42S22): Unknown column 'minvalue' in 'field list'
|
|
drop sequence seq;
|
|
drop table if exists t1, t2;
|
|
create table t1(a int, b int, c int, d int, e int, index idx(a));
|
|
create table t2(a int, b int, c int);
|
|
update t1 join t2 on t1.a=t2.a set t1.a=1 where t2.b=1 and t2.c=2;
|
|
drop table if exists t1, t2;
|
|
create table t1 (a int default 1, b int default 2);
|
|
insert into t1 values (10, 10), (20, 20);
|
|
update t1 set a=default where b=10;
|
|
select * from t1;
|
|
a b
|
|
1 10
|
|
20 20
|
|
update t1 set a=30, b=default where a=20;
|
|
select * from t1;
|
|
a b
|
|
1 10
|
|
30 2
|
|
update t1 set a=default, b=default where a=30;
|
|
select * from t1;
|
|
a b
|
|
1 10
|
|
1 2
|
|
insert into t1 values (40, 40);
|
|
update t1 set a=default, b=default;
|
|
select * from t1;
|
|
a b
|
|
1 2
|
|
1 2
|
|
1 2
|
|
update t1 set a=default(b), b=default(a);
|
|
select * from t1;
|
|
a b
|
|
2 1
|
|
2 1
|
|
2 1
|
|
create table t2 (a int default 1, b int generated always as (-a) virtual, c int generated always as (-a) stored);
|
|
insert into t2 values (10, default, default), (20, default, default);
|
|
update t2 set b=default;
|
|
select * from t2;
|
|
a b c
|
|
10 -10 -10
|
|
20 -20 -20
|
|
update t2 set a=30, b=default where a=10;
|
|
select * from t2;
|
|
a b c
|
|
30 -30 -30
|
|
20 -20 -20
|
|
update t2 set c=default, a=40 where c=-20;
|
|
select * from t2;
|
|
a b c
|
|
30 -30 -30
|
|
40 -40 -40
|
|
update t2 set a=default, b=default, c=default where b=-30;
|
|
select * from t2;
|
|
a b c
|
|
1 -1 -1
|
|
40 -40 -40
|
|
update t2 set a=default(a), b=default, c=default;
|
|
select * from t2;
|
|
a b c
|
|
1 -1 -1
|
|
1 -1 -1
|
|
update t2 set a=default(b), b=default, c=default;
|
|
select * from t2;
|
|
a b c
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
update t2 set b=default(a);
|
|
Error 3105 (HY000): The value specified for generated column 'b' in table 't2' is not allowed.
|
|
update t2 set a=default(a), c=default(c);
|
|
select * from t2;
|
|
a b c
|
|
1 -1 -1
|
|
1 -1 -1
|
|
update t2 set a=default(b), b=default(b);
|
|
select * from t2;
|
|
a b c
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
update t2 set a=default(a), c=default(c);
|
|
select * from t2;
|
|
a b c
|
|
1 -1 -1
|
|
1 -1 -1
|
|
update t2 set a=default(a), c=default(a);
|
|
Error 3105 (HY000): The value specified for generated column 'c' in table 't2' is not allowed.
|
|
drop table t1, t2;
|
|
drop table if exists msg, detail;
|
|
create table msg (id varchar(8), b int, status int, primary key (id, b));
|
|
insert msg values ('abc', 1, 1);
|
|
create table detail (id varchar(8), start varchar(8), status int, index idx_start(start));
|
|
insert detail values ('abc', '123', 2);
|
|
UPDATE msg SET msg.status = (SELECT detail.status FROM detail WHERE msg.id = detail.id);
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
admin check table msg;
|
|
drop table if exists ttt;
|
|
CREATE TABLE ttt (id bigint(20) NOT NULL, host varchar(30) NOT NULL, PRIMARY KEY (id), UNIQUE KEY i_host (host));
|
|
insert into ttt values (8,8),(9,9);
|
|
begin;
|
|
update ttt set id = 0, host='9' where id = 9 limit 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
delete from ttt where id = 0 limit 1;
|
|
select * from ttt use index (i_host) order by host;
|
|
id host
|
|
8 8
|
|
update ttt set id = 0, host='8' where id = 8 limit 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
delete from ttt where id = 0 limit 1;
|
|
select * from ttt use index (i_host) order by host;
|
|
id host
|
|
commit;
|
|
admin check table ttt;
|
|
drop table ttt;
|
|
drop table if exists a;
|
|
create table a(id int auto_increment, a int default null, primary key(id));
|
|
insert into a values (1, 1001), (2, 1001), (10001, 1), (3, 1);
|
|
update a set id = id*10 where a = 1001;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
drop table a;
|
|
create table a ( a bigint, b bigint);
|
|
insert into a values (1, 1001), (2, 1001), (10001, 1), (3, 1);
|
|
update a set a = a*10 where b = 1001;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
drop table if exists items, month;
|
|
CREATE TABLE items (id int, price TEXT);
|
|
insert into items values (11, "items_price_11"), (12, "items_price_12"), (13, "items_price_13");
|
|
affected rows: 3
|
|
info: Records: 3 Duplicates: 0 Warnings: 0
|
|
CREATE TABLE month (mid int, mprice TEXT);
|
|
insert into month values (11, "month_price_11"), (22, "month_price_22"), (13, "month_price_13");
|
|
affected rows: 3
|
|
info: Records: 3 Duplicates: 0 Warnings: 0
|
|
UPDATE items, month SET items.price=month.mprice WHERE items.id=month.mid;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
begin;
|
|
SELECT * FROM items;
|
|
id price
|
|
11 month_price_11
|
|
12 items_price_12
|
|
13 month_price_13
|
|
commit;
|
|
UPDATE items join month on items.id=month.mid SET items.price=month.mid;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
begin;
|
|
SELECT * FROM items;
|
|
id price
|
|
11 11
|
|
12 items_price_12
|
|
13 13
|
|
commit;
|
|
UPDATE items T0 join month T1 on T0.id=T1.mid SET T0.price=T1.mprice;
|
|
affected rows: 2
|
|
info: Rows matched: 2 Changed: 2 Warnings: 0
|
|
begin;
|
|
SELECT * FROM items;
|
|
id price
|
|
11 month_price_11
|
|
12 items_price_12
|
|
13 month_price_13
|
|
commit;
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
create table t1 (c int);
|
|
create table t2 (c varchar(256));
|
|
insert into t1 values (1), (2);
|
|
insert into t2 values ("a"), ("b");
|
|
update t1, t2 set t1.c = 10, t2.c = "abc";
|
|
affected rows: 4
|
|
info: Rows matched: 4 Changed: 4 Warnings: 0
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
create table t1 (c1 int);
|
|
create table t2 (c2 int);
|
|
insert into t1 values (1), (2);
|
|
insert into t2 values (1), (2);
|
|
update t1, t2 set t1.c1 = 10, t2.c2 = 2 where t2.c2 = 1;
|
|
affected rows: 3
|
|
info: Rows matched: 3 Changed: 3 Warnings: 0
|
|
select * from t1;
|
|
c1
|
|
10
|
|
10
|
|
drop table if exists t;
|
|
create table t (a int, b int);
|
|
insert into t values(1, 1), (2, 2), (3, 3);
|
|
affected rows: 3
|
|
info: Records: 3 Duplicates: 0 Warnings: 0
|
|
update t m, t n set m.a = m.a + 1;
|
|
affected rows: 3
|
|
info: Rows matched: 3 Changed: 3 Warnings: 0
|
|
select * from t;
|
|
a b
|
|
2 1
|
|
3 2
|
|
4 3
|
|
update t m, t n set n.a = n.a - 1, n.b = n.b + 1;
|
|
affected rows: 3
|
|
info: Rows matched: 3 Changed: 3 Warnings: 0
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
2 3
|
|
3 4
|
|
drop table if exists update_modified;
|
|
create table update_modified (col_1 int, col_2 enum('a', 'b'));
|
|
set SQL_MODE='';
|
|
insert into update_modified values (0, 3);
|
|
SELECT * FROM update_modified;
|
|
col_1 col_2
|
|
0
|
|
set SQL_MODE=STRICT_ALL_TABLES;
|
|
update update_modified set col_1 = 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT * FROM update_modified;
|
|
col_1 col_2
|
|
1
|
|
update update_modified set col_1 = 2, col_2 = 'c';
|
|
Error 1265 (01000): Data truncated for column '%s' at row %d
|
|
SELECT * FROM update_modified;
|
|
col_1 col_2
|
|
1
|
|
update update_modified set col_1 = 3, col_2 = 'a';
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT * FROM update_modified;
|
|
col_1 col_2
|
|
3 a
|
|
drop table if exists update_with_diff_type;
|
|
CREATE TABLE update_with_diff_type (a int, b JSON);
|
|
INSERT INTO update_with_diff_type VALUES(3, '{"a": "测试"}');
|
|
UPDATE update_with_diff_type SET a = '300';
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT a FROM update_with_diff_type;
|
|
a
|
|
300
|
|
UPDATE update_with_diff_type SET b = '{"a": "\\u6d4b\\u8bd5"}';
|
|
affected rows: 0
|
|
info: Rows matched: 1 Changed: 0 Warnings: 0
|
|
SELECT b FROM update_with_diff_type;
|
|
b
|
|
{"a": "测试"}
|
|
set SQL_MODE=default;
|
|
drop table if exists parent, child;
|
|
create table parent (id int primary key, ref int, key(ref));
|
|
create table child (id int primary key, ref int, foreign key (ref) references parent(ref));
|
|
insert into parent values (1, 1), (2, 2);
|
|
insert into child values (1, 1);
|
|
update child set ref = 2 where id = 1;
|
|
update child set ref = 3 where id = 1;
|
|
Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`executor__update`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
update ignore child set ref = 3 where id = 1;
|
|
Level Code Message
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`executor__update`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
update parent set ref = 3 where id = 2;
|
|
Error 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`executor__update`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
update ignore parent set ref = 3 where id = 2;
|
|
DROP TABLE IF EXISTS t, tp;
|
|
CREATE TABLE t (a INT, b INT, dt DATE, PRIMARY KEY (a) NONCLUSTERED);
|
|
CREATE TABLE tp (a INT, b INT, dt DATE, PRIMARY KEY (a) NONCLUSTERED)
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (5),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (20)
|
|
);
|
|
INSERT INTO tp(a,b) VALUES (2,2),(4,4),(6,6);
|
|
INSERT INTO t(a,b) VALUES (12,12),(14,14),(15,15),(16,16);
|
|
SELECT *, _tidb_rowid FROM t ORDER BY a;
|
|
a b dt _tidb_rowid
|
|
12 12 NULL 1
|
|
14 14 NULL 2
|
|
15 15 NULL 3
|
|
16 16 NULL 4
|
|
SELECT *, _tidb_rowid FROM tp ORDER BY a;
|
|
a b dt _tidb_rowid
|
|
2 2 NULL 1
|
|
4 4 NULL 2
|
|
6 6 NULL 3
|
|
ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t;
|
|
SELECT *, _tidb_rowid FROM tp ORDER BY a;
|
|
a b dt _tidb_rowid
|
|
2 2 NULL 1
|
|
4 4 NULL 2
|
|
6 6 NULL 3
|
|
12 12 NULL 1
|
|
14 14 NULL 2
|
|
15 15 NULL 3
|
|
16 16 NULL 4
|
|
UPDATE tp SET dt = '2025-12-16';
|
|
SELECT *, _tidb_rowid FROM tp ORDER BY a;
|
|
a b dt _tidb_rowid
|
|
2 2 2025-12-16 1
|
|
4 4 2025-12-16 2
|
|
6 6 2025-12-16 3
|
|
12 12 2025-12-16 1
|
|
14 14 2025-12-16 2
|
|
15 15 2025-12-16 3
|
|
16 16 2025-12-16 4
|
|
UPDATE tp SET b = 333;
|
|
SELECT *, _tidb_rowid FROM tp ORDER BY a;
|
|
a b dt _tidb_rowid
|
|
2 333 2025-12-16 1
|
|
4 333 2025-12-16 2
|
|
6 333 2025-12-16 3
|
|
12 333 2025-12-16 1
|
|
14 333 2025-12-16 2
|
|
15 333 2025-12-16 3
|
|
16 333 2025-12-16 4
|
|
DROP TABLE t, tp;
|