Updating table based on same table with a max value-CodePudding

Have a table data structure like below:

id	regid	docid
1	1000	1
2	1000	2
3	1000	3
4	2000	1
5	2000	2
6	3000	1
7	3000	2
8	3000	3
9	3000	4

What I'm trying to do update the archived column to 1 where the docid is less than the max docid, by each regid group.

So I should end up with id's 3, 5 & 9 not being set to 1

Have tried:

update table t1
join (select max(docid) as maxdocid, regid from table) t2 on t1.docid < t2.maxdocid and t1.regid = t2.regid
set t1.archived = 1
where t1.archived = 0

But doesn't work, only does the first regid group.

CodePudding user response：

In steps:

Create a query with the MAX value, per docid:

SELECT
  ID,
  regid,
  docid,
  (SELECT MAX(docid) FROM t1 te where te.regid=t.regid) as M
FROM t1 t

Join the result, and update:

UPDATE t1
JOIN (

  SELECT
    ID,
    regid,
    docid,
    (SELECT MAX(docid) FROM t1 te where te.regid=t.regid) as M
  FROM t1 t

) x ON t1.id=x.id 
SET archived = 1
WHERE t1.docid<x.M AND t1.archived=0;

see: DBFIDDLE

CodePudding user response：

You could try:

update test_tbl t1 
set t1.archived = 1 
where t1.archived = 0 
and t1.id not in ( select t2.id 
                   from (select max(id) as id,
                                regid,
                                max(docid)
                         from test_tbl 
                         group by regid
                         ) as t2 
                   ) ;

Result:

id  regid    docid  archived
1   1000       1    1
2   1000       2    1
3   1000       3    0
4   2000       1    1
5   2000       2    0
6   3000       1    1
7   3000       2    1
8   3000       3    1
9   3000       4    0

Demo

Or you can use a LEFT JOIN

update test_tbl t1 
left join ( select max(id) as id,
                   regid,
                   max(docid) as docid
                   from test_tbl 
                    group by regid
          ) as t2 on t1.id=t2.id
set t1.archived = 1 
where t1.archived = 0
and  t2.id IS NULL

Demo

CodePudding user response：

Use a self join in the update statement:

UPDATE tablename t1
INNER JOIN tablename t2
ON t2.regid = t1.regid AND t2.docid > t1.docid 
SET t1.archived = 1;

See the demo.

CodePudding user response：

Here's a solution (in MySQL 8.0 ) using a CTE:

WITH numbered_table AS (
 SELECT id, ROW_NUMBER() OVER (PARTITION BY regid ORDER BY docid DESC) AS rownum
 FROM mytable
)
UPDATE mytable JOIN numbered_table USING (id)
SET archived = 1
WHERE rownum > 1
AND archived = 0;

Second solution, if you use an older version of MySQL that doesn't support CTE syntax:

You don't really need to compute the max docid value. If you want to update all rows except for the row with the max docid value, then you can check if a row can be matched to any other row with a greater docid value.

UPDATE mytable AS t1
INNER JOIN mytable AS t2 ON t1.regid = t2.regid AND t1.docid < t2.docid
SET t1.archived = 1
WHERE t1.archived = 0;

This will be true for all rows except the row with the max value. That row will be excluded automatically by the join.