How to combine SELECT and UPDATE MySQL

Question

I'm aggregating data and in a table and basically moving the data from one table to the next.

Currently, I'm selecting all the data from MySQL and then inserting it back into the new table.

Is there a way to combine this query to do it all within MySQL rather than taking the data out and then back in?

Basically, I want to combine the following:

'''update landing_pages_v3 set mobile_first_place_rankings=%s where id=%s''', data

'''select count(keyword), lp_id from keywords_v3 where profile_id=%s and device=%s and positions=1 group by lp_id''', (profile_id, device)

select into should do the job https://stackoverflow.com/questions/16809393/select-into-in-mysql — Krzysztof Czerwiński, Oct 01 '18 at 20:18
Check [`INSERT INTO SELECT ...`](https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html) — Madhur Bhaiya, Oct 01 '18 at 20:21

score 1 · Accepted Answer · answered Oct 01 '18 at 20:30

1

You can JOIN the the query as a subquery in the UPDATE:

UPDATE landing_pages_v3 AS l
JOIN (
    SELECT count(*) AS ct, lp_id
    FROM keywords_v3
    WHERE profile_id=%s AND device=%s AND positions=1
    GROUP BY lp_id
) AS k ON l.id = k.lp_id
SET mobile_first_place_rankings = k.ct

answered Oct 01 '18 at 20:30

Barmar

741,623
53
500
612

works perfect - I was pulling all the data out and then putting it back in. This seems a lot quicker! – Adders Oct 03 '18 at 23:06

How to combine SELECT and UPDATE MySQL

1 Answers1