CODEWITHSUNDEEP
mysqlsql
Anriëtte Myburgh
Anriëtte Myburgh

Reputation: 13517

SELECT a list of id's and update those rows

I have the following MySQL statement:

UPDATE xxx_iop_user_plans
SET state = 1, accepted = 1
WHERE id IN (SELECT iup1.id FROM xxx_iop_user_plans iup1
INNER JOIN xxx_iop_user_ed iue ON iup1.user_id = iue.user_id
WHERE iue.parent_id = 44 AND iup1.iop_id = 11)

How should I update this statement to update the rows with an id number present in the list returned from the SELECT statement?

Upvotes: 0

Views: 684

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

You should use the MySQL Multiple-table update syntax, like this:

UPDATE xxx_iop_user_plans
  INNER JOIN xxx_iop_user_ed ON xxx_iop_user_plans.user_id = xxx_iop_user_ed.user_id
SET state = 1, accepted = 1
WHERE xxx_iop_user_ed.parent_id = 44 
  AND xxx_iop_user_plans.iop_id = 11;

Upvotes: 0

John Bingham
John Bingham

Reputation: 2006

I would have expected your update statement to do that. If it isnt updating all of those IDs what is it doing? You could reorganise the query to put the select subquery in the from clause:

UPDATE x 
SET state = 1, accepted = 1
FROM xxx_iop_user_plans x 
    INNER JOIN (
        SELECT iup1.id 
        FROM xxx_iop_user_plans iup1
            INNER JOIN xxx_iop_user_ed iue ON iup1.user_id = iue.user_id
        WHERE iue.parent_id = 44 AND iup1.iop_id = 11
    ) y ON x.ID = y.ID

But I wouldn't expect this to behave any differently from what you already have. Still try it and see.

Upvotes: 1

Related Questions