Returning the 1st row of a SELECT Sub Query

rit01 · February 21st, 2006, 10:41 AM

Hi All

I wonder if someone knows the answer... I have a stored proc that looks similar to the following:

Code:

Update TABLE1
Set
FIELD2 = (SELECT FIELDB FROM TABLE2 WHERE (FIELDA= @PARAM1))
WHERE (FIELD1 = @PARAM1)

The SELECT sub query runs fine when selecting a value from TABLE2 that only appears once but TABLE2 also consists of multiple instance.. it then throws an error.

I think the answer would be to be able to select the FIRST row of the SELECT query result... is this possible in SQL 2K?

Many thanks

Rit

SqlMenace · February 21st, 2006, 10:46 AM

How about

Update TABLE1
Set
FIELD2 = (SELECT TOP 1 FIELDB FROM TABLE2 WHERE (FIELDA= @PARAM1))
WHERE (FIELD1 = @PARAM1)

How do you know that that is the value that you want? do you have duplicates or do you have different values and you don't care what you are updating it with?

âI sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

rit01 · February 21st, 2006, 12:20 PM

that works great... I have checked whether the first record is sufficient... I know what you mean though.

Many thanks

Rit