CODEWITHSUNDEEP
mysqlsql
Mikelo
Mikelo

Reputation: 281

Insert value into subquery

I'm trying to insert the value '1' into column 'isTransfer' of every result of an subquery, but it's not working. This is the query where I select the rows:

select r.* 
from players r 
inner join ( 
   select name, rating, max(id) id 
   from players group by name, rating 
   having count(distinct club) > 1 
   ) 
q on r.name = q.name and r.rating = q.rating and r.id = q.id

This is what I'm trying to do:

INSERT INTO 'isTransfer' VALUES '1' WHERE
(select r.* 
    from players r 
    inner join ( 
       select name, rating, max(id) id 
       from players group by name, rating 
       having count(distinct club) > 1 
       ) 
    q on r.name = q.name and r.rating = q.rating and r.id = q.id)

Upvotes: 1

Views: 43

Answers (2)

AdamMc331
AdamMc331

Reputation: 16730

For this task, you need to do an UPDATE query. Also, you cannot use the WHERE clause like that, you will get an error. Instead, change the where clause to look where the primary key is returned by the subquery. It would look something like this:

UPDATE myTable
SET isTransfer = 1
WHERE primaryKey IN [mySubquery];

You need to make sure that the only column in your SELECT of the subquery is the primary key, otherwise you will get an invalid operand count error.

In regards to your query in the comments, the JOIN is not necessary. Instead, just get the distinct id values from the subquery like this:

SELECT DISTINCT id
FROM(
   SELECT name, rating, MAX(id) AS id
   FROM players
   GROUP BY name, rating
   HAVING COUNT(DISTINCT club) > 1) q

Then, but that query as your IN operand.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Assuming the id is unique in the players table:

update players r inner join
       (select name, rating, max(id) as id 
        from players p
        group by name, rating 
        having count(distinct club) > 1 
       ) nr
       on r.id = nr.id
    set isTransfer = 1;

Upvotes: 2

Related Questions