mysql查询是否更改行?

6ljaweal  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(371)

我有两张table( account2017 )以及( account2018 )我想添加一个 2 在名字的末尾 name 一个表的行,但仅当两个表包含相同的名称时。为该名称添加2,该名称具有较低的 points 价值观。
总的来说,合并具有唯一键(名称)的两个表的解决方案,但要决定哪一行的名称末尾添加了2,应该在后面加上较低的点。
例如,如果表account2017和account2018在 name 列,添加 2 在该表的名称(=alex2)的末尾,该表在 points 列。由于accounts207中的alex得到20分,accounts2018中的alex只有15分,accounts2018中的alex name将更改为alex2。帐户2017将保持不变。
你知道怎么做吗?

vwhgwdsa

vwhgwdsa1#

您需要在两个查询中完成此操作。语法将取决于您使用的内容(mysql、sql server、sqlite),但以下是mysql版本:

UPDATE accounts2017 table1 SET name = concat(name, '2') WHERE exists (SELECT 1 FROM accounts2018 table2 WHERE table1.name = table2.name AND table1.score < table2.score);

然后只需翻转查询即可更新2018表。

icnyk63a

icnyk63a2#

您可以使用多表更新来实现这一点。这依赖于mysql,因为它没有对account2017进行任何更改(时间戳没有更改这一事实证明了这一点),但是在触发之前和之后都要小心。

MariaDB [sandbox]> drop table if exists account2017,account2018;
Query OK, 0 rows affected (0.39 sec)

MariaDB [sandbox]> create table account2017(name varchar(10), points int,ts timestamp  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.20 sec)

MariaDB [sandbox]> create table account2018(name varchar(10), points int,ts timestamp  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.29 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> drop trigger if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [sandbox]> delimiter $$
MariaDB [sandbox]> create trigger t before update on account2017
    -> for each row
    -> begin
    -> insert into debug_table(msg) values (concat('before:',old.name,':',new.name));
    -> end $$
Query OK, 0 rows affected (0.07 sec)

MariaDB [sandbox]> delimiter ;
MariaDB [sandbox]>
MariaDB [sandbox]> drop trigger if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [sandbox]> delimiter $$
MariaDB [sandbox]> create trigger t1 after update on account2017
    -> for each row
    -> begin
    -> insert into debug_table(msg) values (concat('after:',old.name,':',new.name));
    -> end $$
Query OK, 0 rows affected (0.08 sec)

MariaDB [sandbox]> delimiter ;
MariaDB [sandbox]>
MariaDB [sandbox]> insert into account2017 (name,points) values('alex',20);
Query OK, 1 row affected (0.02 sec)

MariaDB [sandbox]> insert into account2018 (name,points) values('alex',15);
Query OK, 1 row affected (0.01 sec)

MariaDB [sandbox]> truncate table debug_table;
Query OK, 0 rows affected (0.17 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select * from account2017;
+------+--------+---------------------+
| name | points | ts                  |
+------+--------+---------------------+
| alex |     20 | 2018-12-11 16:49:25 |
+------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [sandbox]> select * from account2018;
+------+--------+---------------------+
| name | points | ts                  |
+------+--------+---------------------+
| alex |     15 | 2018-12-11 16:49:25 |
+------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

MariaDB [sandbox]> update account2017 join account2018 on account2017.name = account2018.name
    -> set account2017.name = case when account2017.points < account2018.points then concat(account2017.name,'2') else account2017.name end,
    ->     account2018.name = case when account2018.points < account2017.points then concat(account2018.name,'2') else account2018.name end
    -> where 1 = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 2  Changed: 1  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select * from account2017;
+------+--------+---------------------+
| name | points | ts                  |
+------+--------+---------------------+
| alex |     20 | 2018-12-11 16:49:25 |
+------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [sandbox]> select * from account2018;
+-------+--------+---------------------+
| name  | points | ts                  |
+-------+--------+---------------------+
| alex2 |     15 | 2018-12-11 16:50:26 |
+-------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [sandbox]> select * from debug_table;
+----+------------------+------+
| id | msg              | MSG2 |
+----+------------------+------+
|  1 | before:alex:alex | NULL |
|  2 | after:alex:alex  | NULL |
+----+------------------+------+
2 rows in set (0.00 sec)
g52tjvyc

g52tjvyc3#

如果我理解正确的话,听起来你需要使用两个单独的 update 语句,使用 exists 要符合标准:

update account2017
set name = concat(name, '2') 
where exists (
    select 1 
    from account2018 
    where account2017.name = account2018.name and account2017.score < account2018.score)
update account2018
set name = concat(name, '2') 
where exists (
    select 1 
    from account2017 
    where account2018.name = account2017.name and account2018.score < account2017.score)

相关问题