I have one table with 3 columns like this table below

What I need to do is to Update the genstat = 'Done' if the stat of a name is Received. Then if a name have a stat = 'Pending', the genstat will should be Pending.

Example: In the table above, the name c has two stat with the same 'Received' so I should update the genstat of the name c with 'Done', and because the name a and b have a 'Pending' stat, the genstat of those two should be 'Pending' too.

So the table will become like this:

Any suggestion how to do this? Thank you.

CodePudding user response：

If there are no pending then all must be received and left join will return null

update t left join(select distinct name from t where stat = 'pending') t1 on t1.name = t.name
set genstat = case when t1.name is null then 'done' else 'pending' end;

or you could count received and compare to all

update t join (select name , 
                sum(case when stat = 'received' then 1 else 0 end) rcnt, count(*) allcnt from t group by name) 
                t1 on t1.name = t.name
set genstat = case when rcnt = allcnt then 'Done' else 'Pending' end;

In essence these are multi table joins as described in the manual

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cf803dc432f6991070dbecd200bb9c9e

CodePudding user response：

your data

DROP  TABLE IF EXISTS tmp_A;
CREATE  TABLE tmp_A (
   name1    VARCHAR(20) NOT NULL
  ,stat    VARCHAR(20) NOT NULL
  ,genstat VARCHAR(20) 
);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('a','Pending',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('a','Received',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('b','Pending',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('c','Received',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('a','Received',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('b','Pending',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('c','Received',NULL);
INSERT INTO tmp_A(name1,stat,genstat) VALUES ('a','Pending',NULL);

to distinguish stat with pending and Received and choose only pending based on alphabet use row_number as follows

SELECT DISTINCT name1,
                stat
FROM  (SELECT name1,
              stat,
              Row_number()
                OVER(
                  partition BY name1
                  ORDER BY stat ASC ) rn
       FROM   tmp_a) AS t2
WHERE  rn = 1   

then join it with table

SELECT t.name1,
       t.stat,
       IF(t1.stat = 'Received', 'Done', t1.stat) AS genstat
FROM   tmp_a AS t
       join (SELECT DISTINCT name1,
                             stat
             FROM  (SELECT name1,
                           stat,
                           Row_number()
                             over(
                               PARTITION BY name1
                               ORDER BY stat ASC ) rn
                    FROM   tmp_a) AS t2
             WHERE  rn = 1) t1
         ON t1.name1 = t.name1