Combine the queries (SELECT + UPDATE)

4313 views mysql
9

I have the queries that I want to combine to one. But I can't seem to get it working. What to do?

This is number one:

SELECT MIN(publish_date) publish_date, magazine_id
FROM published
GROUP BY magazine_id

This is number two:

UPDATE magazine SET first_published_on = :publish_date WHERE id = :magazine_id

In this case I want to use the data from the SELECT query into the UPDATE query.

Thanks in advance!

answered question

select and update are two commands that should execute separately. you need to use stored procedure to do multiple tasks. do within transaction so this will be unique.

@e03050 Plainly, that's not quite correct.

1 Answer

10

You can try below using UPDATE JOIN

UPDATE magazine
INNER JOIN (
    SELECT MIN(publish_date) publish_date, magazine_id
    FROM   published
    GROUP BY magazine_id
) a ON a.magazine_id=magazine.id
SET first_published_on = a.publish_date

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories