Getting KEY field from updated row w/ no Ident

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting KEY field from updated row w/ no Ident

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-14 : 15:09:36

I'm using this query to update the top 1 row:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
set rowcount 0

The key field on the table is not an identity field.

Is there a way to get that KEY field's data (for the row that was updated)?

I tried this just to see what would happen and it returns null:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
select @@IDENTITY
set rowcount 0

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 15:30:03

@@identity and scope_ident() only applies to identity columns. It looks like you are intentionally updating a random row where sid=999 and status=2. I Assume there can be multiple rows where sid=999 and status=2?

There is no way to know which random row was updated. Perhaps you can select one random PK value using the same logic and then do the update for the PK you just retrieved.

Be One with the Optimizer
TG

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 15:30:22

@@IDENTITY returns last inserted Identity value, but it won't work for updates. Since your table's key field is not an identity column, there is no question of using @@IDENTITY.

What's the key field in your table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-14 : 15:54:08

The key field is called Data and it just contains a unique data value.

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-14 : 19:19:15

wait, my query returns random rows?

What I wanted to do was something like:
update top 1 from mytable where status=x

but what I'm showing above was the best I came up with.

Is there a better way?

thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-14 : 19:34:39

If you are using SQL 2005, you can use the output clause on your Update statement to return data from the rows that are updated.

You can refer to SQL Server 2005 Books Online for details.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 19:41:47

>>Is there a better way?
A better way to do what?
If you wanted to explain your objective to someone that didn't know sql (like your pointy haired boss) what would you say?

Be One with the Optimizer
TG

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-14 : 20:17:44

Some way to do something akin to this:
update top 1 set status=y from mytable where status=x

instead of this:
Set rowcount 1 Update MyTable set Status = X where Status=2
set rowcount 0

I'm not on SQL 2005.

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-14 : 21:36:47

anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 00:10:22

Try like this:-

UPDATE t1
SET t1.Status='y'
FROM @temp t1
INNER JOIN(SELECT t.ID,
t.Status,
(SELECT COUNT(*) + 1 FROM @temp WHERE Status='x' AND ID<t.ID) AS RowNo
FROM @temp t
WHERE t. Status='x') tmp
ON tmp.ID=t1.ID
WHERE tmp.RowNo=1

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-15 : 10:48:14

You're a gentleman and a scholar visakh16!

That's it!

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 12:06:39

Cheers buddy :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources