Update table values from another table with the same user name

user_197036 0
Viewed 95023

I have two tables, with a same column named user_name, saying table_a, table_b.

I want to, copy from table_b, column_b_1, column_b2, to table_b1, column_a_1, column_a_2, respectively, where the user_name is the same, how to do it in SQL statement?

sqlite sql
6 Answers

It could be achieved using UPDATE FROM syntax:

UPDATE table_a
SET column_a_1 = table_b.column_b_1
   ,column_a_2 = table_b.column_b_2
FROM table_b
WHERE table_b.user_name = table_a.user_name;

Alternatively:

UPDATE table_a
SET (column_a_1, column_a_2) = (table_b.column_b_1, table_b.column_b_2)
FROM table_b
WHERE table_b.user_name = table_a.user_name;

UPDATE FROM - SQLite version 3.33.0

The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows

user_50708790

Update tbl1 Set field1 = values field2 = values Where primary key in tbl1 IN ( select tbl2.primary key in tbl1 From tbl2 Where tbl2.primary key in tbl1 = values);

user_168895060

The accepted answer was very slow for me, which is in contrast to the following:

CREATE TEMPORARY TABLE t1 AS SELECT c_new AS c1, table_a.c2 AS c2 FROM table_b INNER JOIN table_a ON table_b.c=table_a.c1;

CREATE TEMPORARY TABLE t2 AS SELECT t1.c1 AS c1, c_new      AS c2 FROM table_b INNER JOIN t1      ON table_b.c=t1.c2;
user_13676550
Related