Update table values from another table with the same user name

Question

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?

martin clayton · Accepted Answer · 2022-04-19T13:37:49.083

147

As long as you have suitable indexes in place this should work alright:

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

UPDATE in sqlite3 did not support a FROM clause for a long time, which made this a little more work than in other RDBMS. UPDATE FROM was implemented in SQLite 3.33 however (2020-08-14) as mentioned at: https://stackoverflow.com/a/63079219/895245

If performance is not satisfactory, another option might be to build up new rows for table_a using a select and join with table_a into a temporary table. Then delete the data from table_a and repopulate from the temporary.

edited Apr 19 '22 at 13:37

answered Oct 02 '10 at 13:31

martin clayton

76,436
32
213
198

4

I had to exclude the table_a part from the left hand side of the set argument, to get this to work. Using the answer above, it looks like `column_a_1 = (SELECT table_b.column_b_1 FROM table_b WHERE table_b.user_name = table_a.user_name )` – Jim Geurts Apr 03 '12 at 22:54
This answer helps understand in more detail how to copy for answer posted at http://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql/17267423#17267423 – zerocog Oct 18 '16 at 21:56
How many times the join is performed here? just 3 times, or instead 3 times per row in table_a? (my SQL is rusty) – toto_tico Apr 25 '17 at 11:28
1

answering my own question, it is 3 times per row, see @Alejadro answer... – toto_tico May 11 '18 at 15:44
Hi, I'm a newbie to SQL. Why are so many WHERE causes necessary? – Llamageddon Nov 20 '19 at 15:22
1

@Llamageddon Because UPDATE in sqlite3 doesn't support a FROM clause, but see below for a newer answer! – martin clayton Nov 20 '19 at 17:59
Suitable indexes really is key here – Finn Espen Gundersen Mar 12 '21 at 10:12
For some reason this is extremely slow for me. It seems much faster to create a temporary table inner joining on one column and then another one inner joining on the other. – Radio Controlled Nov 27 '21 at 09:03
WARNING: If both values are NULL this is not counted as them being equal in the condition! – Radio Controlled Dec 10 '21 at 12:14

Alejadro Xalabarder · Answer 2 · 2022-04-15T15:32:38.210

Starting from the sqlite version 3.15 the syntax for UPDATE admits a column-name-list in the SET part so the query can be written as

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

which is not only shorter but also faster

the last "WHERE EXISTS" part

WHERE
    EXISTS (
       SELECT *
       FROM table_b
       WHERE table_b.user_name = table_a.user_name
   )

is actually not necessary

Lukasz Szozda · Answer 3 · 2020-08-18T16:29:48.067

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

The second variant was way faster than the first one. Thank you! — David, Jun 02 '22 at 20:24

score 2 · Answer 4 · edited Jun 13 '17 at 15:08

2

There is an even much better solution to update one table from another table:

;WITH a AS
(
    SELECT
        song_id,
        artist_id
    FROM
        online_performance
)
UPDATE record_performance
SET
    op_song_id=(SELECT song_id FROM a),
    op_artist_id=(SELECT artist_id FROM a)

;

edited Jun 13 '17 at 15:08

Community

1
1

answered Jul 30 '16 at 00:30

TheFlyingDutchMoose

65
5

1

Since the above is looking up specific rows (record_id= 2347), one would have to write the above code 1000 times to update 1000 rows? – Rasmus Larsen Jun 12 '17 at 15:04
1

This seems promising, but it won't work when you need to join both tables on a common column (such as `user_name` in the original question). Instead, this will set all corresponding values in the table being updated to those of only one record in the source table. Tried listing the common column and adding a `WHERE` condition, but it did not work. – ᴍᴇʜᴏᴠ Aug 13 '17 at 10:24
1

**UPDATE** Actually, my attempt was correct. I might've had an extra line in the query and it said *no error* for some reason, which confused me. The solution was indeed to add the required column to the first select, and then make all other selects refer to it e.g. `(SELECT song_id FROM a WHERE guid=record_performance.guid)` – ᴍᴇʜᴏᴠ Aug 13 '17 at 11:52

score 0 · Answer 5 · answered Sep 12 '21 at 01:20

0

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);

answered Sep 12 '21 at 01:20

olan bocog latibo

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 12 '21 at 01:26

score -1 · Answer 6 · answered Nov 27 '21 at 09:16

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;

Update table values from another table with the same user name

6 Answers6

Linked

Related