Batch SQL query to update a field from different table

Question

Table t1 - Fields a, b Table t2 - Fields a, c

(a is common field between two tables)

I have added the field b in t2 and want to populate data for the same using references in t1

This can be done using following queries (e.g. a = 100, 101, ...)

update t2 set b = (select b from t1 where a = 100 limit 1) where a = 100;

update t2 set b = (select b from t1 where a = 101 limit 1) where a = 101;

Is there way this can be batched?

Gordon Linoff · Accepted Answer · 2018-08-04 17:56:43Z

1

Use join:

update t2 join
       t1
       on t2.a = t1.a
    set t2.b = t1.b;

You could also use a correlated subquery:

update t2
    set b = (select t1.b from t1 where t1.a = t2.a);

However, non-matching values would be set to NULL.

answered Aug 4, 2018 at 17:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1