MySQL update specific column

Question

Let's say I have a table like below:

id user_id   name   custom_field     status
1    123     lee     firstname         1
2    123     tan     lastname          1
3    123     chan     firstname        1
4    123     jackie   lastname         1
5    123     justin   firstname        0
6    123     wong     lastname         0
7    223     kevin    firstname        1
8    223     riley    lastname         1
9    223      john    firtname         1
10   223      poh     lastname         1

What i want to do is, foreach user_id i want to make sure there is only status 1 for 1 firstname and 1 lastname. (so basically i want to order by the id desc and update status 1 to 0 for those that are not in the top 2). What sql script should i use to accomplish this?

And please add your expected result set also

Ullas
– Ullas

2017-02-28 03:21:53 +00:00
Commented Feb 28, 2017 at 3:21 — Ullas
– Ullas, Commented Feb 28, 2017 at 3:21

Gordon Linoff · Accepted Answer · 2017-02-28 03:46:37Z

2

You can do this using an update query such as this:

update t join
       (select userid,
               max(case when custom_field = 'firstname' then id end) as maxid_first,
               max(case when custom_field = 'lastname' then id end) as maxid_last
        from t
        group by userid
       ) tt
       on tt.userid = t.userid and t.id not in (tt.maxid_first, tt.maxid_last)
    set status = 0;

EDIT:

The above does not guarantee that there is at least one "1". For that:

update t join
       (select userid,
               max(case when custom_field = 'firstname' then id end) as maxid_first,
               max(case when custom_field = 'lastname' then id end) as maxid_last
        from t
        group by userid
       ) tt
       on tt.userid = t.userid
    set status = (t.id in (tt.maxid_first, tt.maxid_last));

edited Feb 28, 2017 at 3:46

answered Feb 28, 2017 at 3:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CloudSeph Over a year ago

works fine but should be max not min. cause i want the last updated one to be status 1 :) thanks.

Collectives™ on Stack Overflow

MySQL update specific column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related