SQL从另一个表替换表中的值

I have two tables and I would like to replace a part of values from table1's column with values from table2's column.

我有两个表，我想用table2的列中的值替换table1列中的一部分值。

To better explain: I would like to put values in column "name" from table2 to column "name" in table1 on rows with id 3-9.

为了更好地解释：我想将值从表2中的“name”列中的值放到table1中列为“name”的列上，其值为3-9。

I'm working in Mysql workbench and MariaDB.

我在Mysql工作台和MariaDB工作。

3 个解决方案

#1

The most straight forward approach is using a subquery in the SET clause.

最直接的方法是在SET子句中使用子查询。

UPDATE table1
       SET name = (SELECT t2.name
                          FROM table2 t2
                          WHERE t2.pid = table1.id)
       WHERE id >= 3
             AND id <= 9;

#2

@stickybit's answer may work just fine, but I thought I would give an alternative using a JOIN because it is more efficient than a subquery and BETWEEN because it makes the syntax a little simpler:

@ stickybit的答案可能工作正常，但我认为我会使用JOIN提供替代方法，因为它比子查询和BETWEEN更有效，因为它使语法更简单：

UPDATE table1
JOIN table2
ON table1.id = table2.pid
SET table1.name = table2.name
WHERE table1.id BETWEEN 3 AND 9;

#3

-1

update table1 t1 
set t1.name=t2.name from table1 t1 ,table2 t2 
where t1.id=t2.id and id between 2 and 10

#1