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

Question

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?

Oleg K. · Accepted Answer · 2015-04-06 22:01:34Z

1

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;

answered Apr 6, 2015 at 22:01

Oleg K.

1,5498 silver badges11 bronze badges

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

1 Comment

MikelG Over a year ago

Yup, knew I was over thinking it. Figured there'd be an easier way. Thanks!

Mr. Mascaro · Accepted Answer · 2015-04-06 22:11:04Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related