Update Top 1 record in table sql server
UPDATE TX_Master_PCBA
SET TIMESTAMP2 = '2013-12-12 15:40:31.593',
G_FIELD='0000'
WHERE TIMESTAMP2 IN
(
SELECT TOP 1 TIMESTAMP2
FROM TX_Master_PCBA WHERE SERIAL_NO='0500030309'
ORDER BY TIMESTAMP2 DESC -- You need to decide what column you want to sort on
)
How to update top 1 row in SQL Server
Try this:
UPDATE CheckInCheckOut
SET CountHours = @Result
WHERE UserId_Fk = (SELECT TOP 1 UserId_Fk
FROM CheckInCheckOut
ORDER BY [Your_Sort_Column])
SQL UPDATE TOP () or UPDATE with SELECT TOP
First statement will be faster. But the top 150 records are chosen randomly. Records updated in both the queries might not be same. Since you are spitting the updates into batches your approach may not update all records.
I will do this using following consistent approach than your approach.
;WITH cte
AS (SELECT TOP (350) value1,
value2,
value3
FROM database1
WHERE value1 = '123'
ORDER BY ID -- or any other column to order the result
)
UPDATE cte
SET value1 = '',
value2 = '',
value3 = ''
Also you don't have to worry transaction log size when updating couple thousands records there is no need of batches here
Update based on group by, top 1 row and where case
Just slightly modify your current query by assigning a row number, across each ParentID
group. The ordering logic for the row number assignment is that records with IsOnTop
values of 1 come first, and after that the OrderBy
column determines position. I update the CTE under the condition that only the first record in each ParentID
group gets assigned a Default
value of 1.
WITH cte AS (
SELECT ParentID, ID, [Default], IsOnTop, OrderBy,
ROW_NUMBER() OVER (PARTITION BY ParentID
ORDER BY IsOnTop DESC, OrderBy) rn
FROM [table]
WHERE ParentID IN (SELECT ParentID FROM [table]
GROUP BY ParentID HAVING SUM([Default]) <> 1)
)
UPDATE cte
SET [Default] = 1
WHERE rn = 1;
SQL UPDATE TOP with ORDER BY?
you can use common table expression for this:
;with cte as (
select top (@MaxRecords)
status
from Messages
where Status = 'N' and InsertDate >= getdate()
order by ...
)
update cte set
status = 'P'
output inserted.*
This one uses the fact that in SQL Server it's possible to update cte, like updatable view.
How to update only one row in a table?
you can use ROWCOUNT
SET ROWCOUNT 1
UPDATE table1
SET name2 = '01'
WHERE name1='xx'
SET ROWCOUNT 0
or you can use update top
UPDATE TOP (1) table1
SET name2 = '01'
WHERE name1='xx'
Update only Top 1 OR anyone row of a table using join with another table
In Table 2
, give a row number based on group by Skill
column and order by MyValue
column. And then updated the rows which having row number1
with Value
from Table 1
.
Query
;with cte as(
select [rn] = row_number() over(
partition by Skill
order by [MyValue]
), *
from [Table2]
)
update t1
set t1.[MyValue2] = t2.[Value]
from cte t1
join [Table1] t2
on t1.[Skill] = t2.[Skill]
where t1.[rn] = 1;
Related Topics
Rails Find Record with Zero Has_Many Records Associated
The Transaction Log for the Database Is Full
How to Execute a Text File from SQL Query
SQL Server Cumulative Sum by Group
MySQL Subquery Returns More Than One Row
Extbase - Get Created SQL from Query
Why Does the Wm_Concat Not Work Here
How to Interpret a Query's Explain Plan
When to Use SQL Sub-Queries Versus a Standard Join
Doesn't Linq to SQL Miss the Point? Aren't Orm-Mappers (Subsonic, etc.) Sub-Optimal Solutions
SQL Like Condition to Check for Integer
SQL Query for Getting Data for Last 3 Months
Partition by with and Without Keep in Oracle
"Column Not Allowed Here" Error in Insert Statement