CODEWITHSUNDEEP
mysqlsqldatabase
Shikhar
Shikhar

Reputation: 377

Batch SQL query to update a field from different table

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?

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 1

Related Questions