CODEWITHSUNDEEP
mysqlsql
Cornwell
Cornwell

Reputation: 3410

Using JOIN in UPDATE query

I have 2 tables that have identical structure:

| term (varchar(50) utf8_bin) | count (bigint(11)) |

One table is called "big_table" and the other one "small_table". Big table has ~10M rows and small_table has 75k.

I want to update small_table, so the count column will be filled from the big_table. I tried this:

UPDATE small_table b SET counter = (SELECT c.counter
                                 FROM big_table c
                                 WHERE c.term = b.term)
WHERE term = (SELECT c.term
              FROM big_table c
              WHERE c.term = b.term);

But it is only updating one row...

Upvotes: 2

Views: 63

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

I think you only need a JOIN:

UPDATE small_table b 
  JOIN big_table c
    ON c.term = b.term
SET b.counter = c.counter ;

Upvotes: 3

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

You don't need WHERE part at all:

UPDATE 
    small_table b 
SET 
    counter = 
        ISNULL
        (
            (
                SELECT c.counter
                FROM big_table c
                WHERE c.term = b.term
            ), 
            0
        )

Think about this way:

UPDATE every row in small_table and SET column counter to the counter value found in big_table record WHERE value of term is equals to term in small_table. If nothing is found in big_table then just set counter to 0.

Upvotes: 2

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

I think below query will help you

          UPDATE small_table SET counter = c.counter
          FROM big_table c INNER JOIN small_table b ON c.term = b.term

Upvotes: 0

Related Questions