RandomUser RandomUser - 5 months ago 20
SQL Question

Update column in SQL Server conditionally

I have the following result stored in a temp table

#tempResult


SourceGuid SourceTypeID IsSelected
-------------------- -------------------- ----------
84588BAF068320450908 129 0
5367D0F80A0F4040E909 658 0 <
872791440CDBE04F7C0B 658 0 <
15ABA6B70E4A70463E0B 887 0
1DE39F9805A8A044B20B 887 0
3F96C93501144041D50A 887 0
6A01DFF705861049F509 887 0


I need to make IsSelected value to 1 of only one and not both of the record marked with
<
to 1 regardless of any other condition just SourceTypeID = 658

How can I achieve this, thanks.

Answer

You could use a CTE to get the TOP 1 and then do the UDPATE:

;WITH CTE AS(
    SELECT TOP (1) *
    FROM #tempResult
    WHERE SourceTypeId = 658
    ORDER BY NEWID()
)
UPDATE CTE SET IsSelected = 1

ORDER BY NEWID() denotes random sort. Replace NEWID() with the column you want the TOP to be based on.

Comments