CODEWITHSUNDEEP
mysql
MikelG
MikelG

Reputation: 479

How to update a table by adding the values from another table?

Ok here's what I have.

stats

unique_key   clicks
abcdefg      17
coolstuf     19

.

temp_stats

unique_key   clickss
coolstuf     32
abcdefg      7

How do I add the clickss from 'temp_stats' to the clicks in 'stats' and update clicks in 'stats?

Stats would look like this after the update...

stats

unique_key   clicks
abcdefg      24
coolstuf     51

Now I've actually managed to figure out how to do this with the following query...

UPDATE stats, (SELECT unique_key, NEW FROM (
  SELECT *, (clicks + clickss) AS NEW FROM (
    SELECT stats.unique_key, stats.clicks, temp_stats.clickss
    FROM stats
    JOIN temp_stats
    ON stats.unique_key=temp_stats.unique_key
  ) AS TEMP1 ) AS TEMP2) AS TEMP3
SET clicks=NEW
WHERE stats.unique_key=TEMP3.unique_key

However, it looks like a mess of a query and I built it from the inside out. Is there a better, more efficient/elegant way to achieve the same thing?

Upvotes: 0

Views: 29

Answers (2)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

Try this:

UPDATE stats
INNER JOIN temp_stats ON stats.unique_Key=temp_stats.unique_Key
SET clicks = clicks + clicks

You don't need a full select to join tables for an update.

Tested on MySQL 5.6

Fiddle

Upvotes: 0

Oleg K.
Oleg K.

Reputation: 1549

You can do JOIN on UPDATE:

UPDATE stats JOIN temp_stats ON stats.unique_key = temp_stats.unique_key
    SET stats.clicks = stats.clicks + temp_stats.clicks;

Upvotes: 1

Related Questions