Using the result of one query inside another query (MySQL)

Question

I need to get a subset of one of my tables, and then use these id's in another query, is this possible?

Ideally, I need to use the result of this query:

SELECT id
FROM table
GROUP BY col1, co12
HAVING COUNT(*) > 1

inside this query:

UPDATE table
SET col1 = CONCAT(col1, '_1')
WHERE id IN (ABOVE_QUERY)

What's the problem.. just insert your first query as is into the brackets of the second query - done. — mhafellner
– mhafellner, Commented Feb 21, 2014 at 16:05

fthiella · Accepted Answer · 2014-02-21 16:07:14Z

1

I think you are looking for something like this:

UPDATE
  table INNER JOIN (SELECT MAX(id) as m_id
                    FROM table
                    GROUP BY col1, co12
                    HAVING COUNT(*) > 1) t1
  ON table.id = t1.m_id
SET col1 = CONCAT(col1, '_1')

In MySQL you need to use a JOIN because you aren't allowed to update a table referenced in a subquery. And you probably need to use an aggregated function on the ID returned by your subquery.

answered Feb 21, 2014 at 16:07

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

Using the result of one query inside another query (MySQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related