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?
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
Syntax: UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition; Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data.
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.
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 ;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With