select the rows affected by an update

0 votes
asked Dec 1, 2010 by poni

If I have a table with this fields:

int:id_account
int:session
string:password

Now for a login statement I run this sql UPDATE command:

UPDATE tbl_name
SET session = session + 1
WHERE id_account = 17 AND password = 'apple'

Then I check if a row was affected, and if one indeed was affected I know that the password was correct.

Next what I want to do is retrieve all the info of this affected row so I'll have the rest of the fields info.
I can use a simple SELECT statement but I'm sure I'm missing something here, there must be a neater way you gurus know, and going to tell me about (:
Besides it bothered me since the first login sql statement I ever written.

Is there any performance-wise way to combine a SELECT into an UPDATE if the UPDATE did update a row?
Or am I better leaving it simple with two statements? Atomicity isn't needed, so I might better stay away from table locks for example, no?

3 Answers

0 votes
answered Dec 1, 2010 by silver-light

You should use the same WHERE statement for SELECT. It will return the modified rows, because your UPDATE did not change any columns used for lookup:

UPDATE tbl_name
SET session = session + 1
WHERE id_account = 17 AND password = 'apple';

SELECT *
FROM tbl_name
WHERE id_account = 17 AND password = 'apple';

An advice: never store passwords as plain text! Use a hash function, like this:

MD5('apple')
0 votes
answered Dec 1, 2010 by unreason

There is ROW_COUNT() (do read about details in the docs).

Following up by SQL is ok and simple (which is always good), but it might unnecessary stress the system.

0 votes
answered Sep 15, 2017 by aaron

This won't work for statements such as...

Update Table
Set Value = 'Something Else'
Where Value is Null

Select Value From Table
Where Value is Null

You would have changed the value with the update and would be unable to recover the affected records unless you stored them beforehand.

Select * Into #TempTable 
From Table
Where Value is Null

Update Table
Set Value = 'Something Else'
Where Value is Null

Select Value, UniqueValue
From #TempTable TT
Join Table T
TT.UniqueValue = T.UniqueValue

If you're lucky, you may be able to join the temp table's records to a unique field within Table to verify the update. This is just one small example of why it is important to enumerate records.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...