您需要在两个查询中完成此操作。语法将取决于您使用的内容（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表。

您可以使用多表更新来实现这一点。这依赖于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)

如果我理解正确的话，听起来你需要使用两个单独的 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)