SELECT and UPDATE a record - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT and UPDATE a record

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-07 : 09:23:30

Svenne Olsen writes "I want to UPDATE the records I just SELECTed, but since there's no trigger for SELECT's, how can this be accomplished?

I wanna do somehin' like set the Status field to 1, whenever the record has been selected. The problem is how do I know what record I just SELECTed?
I guess this introduces somethin' with LOCK's and CURSOR's, but can you help me figure it out?

Pseudo code:


SELECT ID,Phone,Name,Status FROM MyTable
UPDATE MyTable SET Status = 1 WHERE ID = 'JUST SELECTED ID'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-07 : 09:35:34

There's no trick to doing what you want to do ...

You'll have to have two seperate DML statements that affect the same rowset.

Select junk from table where condition
update table status = 1 where same condition

Jay White
{0}

lfmn
Posting Yak Master

141 Posts

Posted - 2003-02-07 : 11:53:53

How about:

UPDATE MyTable SET Status = 1
WHERE ID in (SELECT ID FROM MyTable)

SQL is useful if you don't know cursors :-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-07 : 12:48:01

lfmn, how is that better (or even different) from my suggestion?

Jay White
{0}

lfmn
Posting Yak Master

141 Posts

Posted - 2003-02-09 : 18:26:53

Page47

It's saying the same thing, I just wrote out the query based on the question and you used pseudo code. Based on the wording of the question, it appeared that Svennee needed exact syntax.

My solution is not better or different, just clearer.

SQL is useful if you don't know cursors :-)

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-10 : 03:52:48

quote:

You'll have to have two seperate DML statements that affect the same rowset.

You can UPDATE the record and SELECT the other values into parameters in the same statment.

UPDATE MyTable SET
@phone = Phone,
@name = Name,
Status = 1
WHERE ID = @id

SELECT @phone, @name ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources