How to update field in If condition will true in mysql?-CodePudding

I have 2 tables called projects, project_doctors. project_doctors has no id field called auto_increments.

Projects and project_doctors is one to many relationships.

Projects table have id 43, 44, 45, etc,.. Project_doctors have 3 rows in project_id = 43 and 44, 4 rows in project_id=45.

I want to get if project_id has 3 records, those order is 0,1,2,… i.e, in figure, order_id is 0, 1, 2 in project_id=43. But, I inserted 4 after I will wrong.

So, I write to mysql query for when project_doctors has 3 records, if largest order_id is not 2 because index 0,1,2. I want to update order_id = 2 for largest order_id.

i.e, when project_doctors has 4 records, if largest order_id is not 3 because index 0,1,2,3. I want to update order_id = 3 for largest order_id.

A little query, I wrote. But not complete. Still remain If condition for how many records for this project_id no.

    UPDATE project_doctors 
SET 
    order_id = IF(order_id != 2,
        2,
        order_id)
where project_id=43 
ORDER BY order_id DESC
LIMIT 1;

CodePudding user response：

Use a subquery to get the count of rows and the maximum order_id. You can then compare these to see if they don't match.

UPDATE project_doctors AS p
JOIN (
    SELECT project_id, COUNT(*) as count, MAX(order_id) AS maxoid
    FROM project_doctors
    GROUP BY project_id
    HAVING maxoid != count - 1
) AS c ON c.project_id = p.project_id AND c.maxoid = p.order_id
SET p.order_id = c.count-1;