update row if count(*) > n

Question

my DB has this structure:

ID | text | time | valid

This is my current code. I'm trying to find a way to do this as one query.

rows = select * from table where ID=x order by time desc;
n=0;
foreach rows{
    if(n > 3){
       update table set valid = -1 where rows[n]; 
    }
    n++
}

I'm checking how many rows exist for a given ID. Then I need to set valid=-1 for all rows where n >3;

Is there a way to do this with one query?

See: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server and http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — Denis de Bernardy, Oct 10 '13 at 13:30

score 5 · Answer 1 · edited Sep 05 '18 at 10:39

5

You can use a subquery in the WHERE clause, like this:

UPDATE table
   SET valid=-1
 WHERE (
         SELECT COUNT(*)
           FROM table tt
          WHERE tt.time > table.time
            AND   tt.ID = table.ID
) > 3

The subquery counts the rows with the same ID and a later time. This count will be three or less for the three latest rows; the remaining ones would have a greater count, so their valid field would be updated.

edited Sep 05 '18 at 10:39

SergeyYu

354
1
5
20

answered Oct 10 '13 at 13:30

Sergey Kalinichenko

714,442
84
1,110
1,523

I get this error when I try this: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Francis Snipe Oct 10 '13 at 13:47
@FrancisSnipe Let me try to construct a demo on ideone. What version of MySql are you using? – Sergey Kalinichenko Oct 10 '13 at 13:49

mucio · Answer 2 · 2013-10-10T15:23:27.093

update table 
   set valid = -1
 where id in (select id
                from table 
               where id = GIVEN_ID
            group by id
              having count(1) >3)

Update: I really like dasblinkenlight's solution because is very neat, but I wanted to try also to do it in my way, a quite verbose one:

  update Table1
     set valid = -1
   where (id, time) in (select id, 
                               time
                          from (select id,time
                                  from table1
                                 where id in (select id
                                                from table1
                                            group by id
                                              having count(1) >3)
                                -- and id = GIVEN_ID
                              order by time 
                                 limit 3, 10000000) 
                        t);

Also in SQLFiddle

This would set `valid` to -1 on all rows where the total count is greater than 3. OP's loop keeps the first three rows unchanged, setting `valid` to `-1` on everything after the 4-th row. — Sergey Kalinichenko, Oct 10 '13 at 13:32

score 2 · Accepted Answer · answered Oct 10 '13 at 14:17

Assuming that (id,time) has a UNIQUE constraint, i.e. no two rows have the same id and same time:

UPDATE 
    tableX AS tu
  JOIN
    ( SELECT time
      FROM tableX
      WHERE id = @X                      -- the given ID
      ORDER BY time DESC
      LIMIT 1 OFFSET 2
    ) AS t3
    ON  tu.id = @X                       -- given ID again
    AND tu.time < t3.time 
SET
    tu.valid = -1 ;

score 0 · Answer 4 · edited Sep 05 '18 at 11:19

to do it for all ids, or only for one if you set a where in the a subquery

UPDATE TABLE
  LEFT JOIN (
              SELECT *
                FROM (
                       SELECT @rn:=if(@prv=id, @rn+1, 1) AS rId,
                              @prv:=id AS id,
                              TABLE.*
                         FROM TABLE
                         JOIN ( SELECT @prv:=0, @rn:=0 ) tmp
                        ORDER BY id, TIMESTAMP 
                     ) a
               WHERE rid > 3
             ) ordered ON ordered.id = TABLE.id
   AND ordered.TIMESTAMP = TABLE.TIMESTAMP
   AND ordered.text = TIMESTAMP.text
   SET VALID = -1
 WHERE rid IS NOT NULL

update row if count(*) > n

4 Answers4