CODEWITHSUNDEEP
mysqlsql
northsideknight
northsideknight

Reputation: 1557

Update half of results of SQL query

In MySQL I need to update half of the records of a query's results to set a column to a certain attribute.

So something like...

UPDATE some_table SET some_column='some_value'
WHERE some_query = 'something' LIMIT (SELECT COUNT(*)/2 FROM some_table);

But this will give me a syntax error. I am wondering if this is possible and how to perform this in MySQL.

Thanks for the help.

Upvotes: 0

Views: 3282

Answers (3)

Strawberry
Strawberry

Reputation: 33945

E.g.:

UPDATE my_table a 
  JOIN 
     ( SELECT x.*, @i:=@i+1 rank FROM my_table x, (SELECT @i:=0) vars ORDER BY id) b 
    ON b.id = a.id 
   SET a.val = 'a' 
 WHERE rank % 2 = 1;

This updates every other row (which is approximately equal to 'half the rows')

Upvotes: 1

Viktor Čajbík
Viktor Čajbík

Reputation: 42

I'm not sure if mysql have function ROW_NUMBER, but in T-SQL you can use somethin like this:

with a (rownum, some_column) 
as 
(select ROW_NUMBER() over (order by some_column) as rownum, some_column 
    from some_table 
    where some_query = 'something')
update a 
    set some_column='some_value'
from a
where rownum <= (select count(1)/2 from a)

Upvotes: 0

Milney
Milney

Reputation: 6417

You could add a row_number and only affect rows whose row number is <= half of the total number of rows... something like;

WITH Numbered as (
SELECT *,
    row_number() as RowNum
FROM some_table 
)
UPDATE Numbered
SET some_row = 'some_value'
WHERE RowNum <= (SELECT Count(*) / 2 FROM Numbered)

Upvotes: 0

Related Questions