CODEWITHSUNDEEP
sqlsql-serverselectcount
Will
Will

Reputation: 23

SQL Server 2008 - Updating only a specific number of rows within a table

I have a table called mytable with a number of columns.
What I am trying to do is to update a column within that table where 2 colums are matched but only for the first 400 records that match the criteria.

Example:

Columns

Name     Ref   ID    Text

When ref = 555 and id = 7 I want to update the column called Text with the word 'Matched'.
I am aware that there will be approx 800 records that match the criteria but I only want to update the first 400.
Can anyone help with this please?

Regards, Will.

Upvotes: 2

Views: 4545

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460340

One easy way is using ROW_NUMBER in a CTE, e.g.:

WITH CTE AS
(
   SELECT rn = ROW_NUMBER() OVER (ORDER BY ref, id),
          ref, id, text
   FROM   MyTable
   WHERE  ref = 555 AND  id = 7
)
UPDATE CTE SET text = 'Matched' 
WHERE RN <= 400

Demo

Upvotes: 8

DrCopyPaste
DrCopyPaste

Reputation: 4137

You can join the table you want to update with itself:

UPDATE MyTable
        SET Text = 'Matched'
    FROM MyTable
    INNER JOIN (
        SELECT TOP 400 ID FROM MyTable WHERE ref = 555 AND ID = 7 ORDER BY ID
    ) AS InnerMyTable ON MyTable.ID = InnerMyTable.ID

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

I have given update trigger if you need insert change update keyword in second line to insert

CREATE TRIGGER T
AFTER UPDATE ON MYTABLE
FOR EACH ROW BEGIN
WHERE (SELEC COUNT(*) FROM MYTABLE WHERE ref = 555 and id = 7) <= 400
BEGIN
UPDATE MYTABLE
SET TEXT = 'MATCHED'
WHERE REF = NEW.REF AND ID = NEW.ID;
END;

Upvotes: 0

Taryn
Taryn

Reputation: 247870

You can use the a CTE to select the TOP 400 rows that have a ref=555 and an id=7:

;with cte as
(
  select top 400 ref, id, text
  from yourtable
  where ref = 555
    and id = 7
  order by ref, id
)
update cte
set text = 'Matched';

See SQL Fiddle with Demo

Upvotes: 4

Related Questions