CODEWITHSUNDEEP
sqlmariadb
Freddy19
Freddy19

Reputation: 187

SQL replace values in a table from another table

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

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

I'm working in Mysql workbench and MariaDB.

Upvotes: 1

Views: 9669

Answers (3)

mtr.web
mtr.web

Reputation: 1525

@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:

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

Upvotes: 2

user9877188
user9877188

Reputation: 11

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

Upvotes: -1

sticky bit
sticky bit

Reputation: 37487

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

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

Upvotes: 3

Related Questions