Updating table based on same table with a max value

user_717192 0
Viewed 81

Have a table data structure like below:

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

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

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

mysql
4 Answers

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

user_164619520

In steps:

  1. 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
  1. 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

user_7240390

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.

user_104988280

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.

Bill_Karwin_208600
Related