Update Half Of Table Rows

user19353552 0
Viewed 42

I have a table of 65,957 rows containing a processed flag column which are all set to 0. I am trying to take half of the rows and change that processed flag column to 5.

Table structure:

person, username, idnumber, code, value, time, processed

A person can be in the table multiple times.

sql-server sql
2 Answers

Don't use WHERE id IN (subquery), you can filter in a common table expression, then update the filtered set directly.

WITH
  sample AS
(
  SELECT TOP 50 PERCENT * FROM table WHERE processed = 0
)
UPDATE
  sample
SET
  processed = 5

Example: https://dbfiddle.uk/7N4CAzGS

MatBailie0 
update <YourTable>
set processed=5
where id 
IN
(
Statements below with ID column
)

To get the first 50% records:

select top 50 percent * from <YOURTABLE>;

To get the last 50% records:

SELECT ID FROM
(SELECT top 50 percent * FROM <YOURTABLE> ORDER BY ID DESC)
ORDER BY ID;
jmvcollaborator0
Related