phpmyadmin Question posted in
The official documentation can be found here.

Update column value that has one or more same values of other column – Phpmyadmin

I have this statement:

UPDATE `tblAllocatedLeads` 
SET tblAllocatedLeads.fldAllocatedStatus = CASE 
WHEN tblAllocatedLeads.fldAllocatedStatus = 'Allocated' 
AND 
(SELECT COUNT(*) FROM (SELECT * FROM `tblAllocatedLeads`) AS tblallocate WHERE AND tblallocate.fldAllocatedLeadId = tblAllocatedLeads.fldAllocatedLeadId) > 1 
THEN 'Outstanding' 
END

What I wanted is that update all rows where fldAllocatedStatus = ‘Allocated’ to ‘Outstanding’ that has one or more same value of column fldAllocatedLeadId to other rows.

my data

ID   fldAllocatedStatus   fldAllocatedLeadId
1    Accepted             123
2    Rejected             123
3    Allocated            123
4    Allocated            321

In this case ID = 3 should update fldAllocatedStatus to ‘Outstanding’ as it has one or more same fldAllocatedLeadId from ID = 1,2

and ID = 4 should stay ‘Allocated’ as it doesn’t have match of fldAllocatedLeadId with other rows.

right now what it does it updates even the “Accepted,Rejected” which is supposedly to update only rows with “Allocated”

Tags:

2

Answers


  1. 0

    Assuming MySQL, I think you want:

    UPDATE tblAllocatedLeads al JOIN
       (SELECT al2.fldAllocatedLeadId
        FROM tblAllocatedLeads al2
        WHERE al.fldAllocatedStatus <> 'Allocated'
        GROUP BY al2.tblAllocatedLeads
       ) al2
       ON al2.tblAllocatedLeads = al.tblAllocatedLeads
    SET al.fldAllocatedStatus = 'Outstanding'
    WHERE al.fldAllocatedStatus = 'Allocated' ;

    The subquery gets leads that have some value that is not allocated. The outer WHERE only updates the allocated row (if there is one).

    Login or Signup to reply.
  2. 0

    You don’t need any aggregation, just a self join:

    UPDATE tblAllocatedLeads t 
INNER JOIN tblAllocatedLeads tt
ON tt.id <> t.id AND tt.fldAllocatedLeadId = t.fldAllocatedLeadId 
SET t.fldAllocatedStatus = 'Outstanding' 
WHERE t.fldAllocatedStatus = 'Allocated';

    See the demo.
    Results:

    | ID  | fldAllocatedStatus | fldAllocatedLeadId |
| --- | ------------------ | ------------------ |
| 1   | Accepted           | 123                |
| 2   | Rejected           | 123                |
| 3   | Outstanding        | 123                |
| 4   | Allocated          | 321                |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search