CODEWITHSUNDEEP
mysqlsqlif-statement
Yan Kyaw Min
Yan Kyaw Min

Reputation: 15

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

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.

enter image description here

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;

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In general, if you want to renumber the orders so there are no gaps, you can use window functions:

UPDATE project_doctors pd JOIN
       (SELECT pd.*, 
               ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY order_id) as new_order_id
        FROM project_doctors pd
       ) pd2
       ON pd2.project_id = pd.project_id AND 
          pd2.order_id = pd.order_id
    SET pd.order_id = pd2.new_order_id
    WHERE pd2.new_order_id <> pd.order_id;

You could have a situation where the order ids are, for example, 1, 3, and 5. Setting 5 to 3 would result in a duplicate.

Upvotes: 0

Barmar
Barmar

Reputation: 780724

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;

Upvotes: 1

Related Questions