CODEWITHSUNDEEP
mysql
Da49
Da49

Reputation: 117

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

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)

Upvotes: 0

Views: 79

Answers (1)

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 1

Related Questions