Member Avatar for Red_Indian

Hi,

I have 20 rows in a table and I want to update 10 of them only.

I am using MS SQL.

Any ideas please.

Cheers

Edited by Red_Indian because: n/a
  • 5 Contributors
  • 6 Replies
  • 105 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by prit005

Dani AI

Generated

The "nameless" first column you see in the results grid is likely the SSMS row header, not a real table column. Tables have no inherent order, so "first 10 rows" is undefined unless you pick them using a stable ordering (for example an IDENTITY or primary key). If the table truly has no key, add one or select rows explicitly before updating.

A reliable pattern is to assign row numbers with ROW_NUMBER() and update the rows where that number is <= N. This makes the operation deterministic when you supply an ORDER BY. Example pattern (replace the column names and ordering key):

WITH to_pick AS (
  SELECT PKCol, ROW_NUMBER() OVER (ORDER BY PKCol) AS rn
  FROM dbo.YourTable
)
UPDATE t
SET ColA = 'newA', ColB = 'newB'
FROM dbo.YourTable t
JOIN to_pick p ON t.PKCol = p.PKCol
WHERE p.rn <= 10;

As pointed out, TOP is another option and 's idea of using specific IDs works when you know them. 's mention of @@ROWCOUNT is handy to verify how many rows were affected; consider also the OUTPUT clause to capture what changed. Avoid relying on an arbitrary row order—always include an ORDER BY source if you need predictable results. Test the query inside a transaction and back up data before mass updates.

Further reading: ROW_NUMBER (Transact-SQL) and UPDATE (Transact-SQL).

Recommended Answers

All 6 Replies

Member Avatar for urtrivedi 
update tablename set col1='newvalue' where col2='my condition'
Member Avatar for rickymartin3

use below procedure
---------------------------


USE testtable;
GO
UPDATE testtable.Employee
SET salary=salary+1000
WHERE Sal>=5000 and sal<=15000
IF @@ROWCOUNT = 10
PRINT 'Count limit is reached.';
GO

Member Avatar for Red_Indian

Thanks for the suggestion.

There are 3 columns. But as you know the very first column is nameless and numbers each row. I cannot use a any of the columns to set a condition e.g. if col1 < 'value1' and > 'value2'. I just want to be able to update 2 columns values up to 10 rows.

Hope that makes sense.

Thanks for the help

Member Avatar for Red_Indian

Cheers.

Can you use the IF @@ROWCOUNT = 10 without the WHERE condition?

Member Avatar for Member #910485

update top(10) MyTable set MyColumn = 22

Member Avatar for prit005

Update EMPLOYEE set EMPNAME='SomeValue' where EMPID in(1,2,3,4,5,6,7,8,9,10)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.