CODEWITHSUNDEEP
sqlmysql
Poni
Poni

Reputation: 11317

select the rows affected by an update

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?

Upvotes: 6

Views: 10258

Answers (4)

Mohsin Ali Khan
Mohsin Ali Khan

Reputation: 11

You can get the effected rows by just using @@RowCount..

select top (Select @@RowCount)  * from YourTable order by 1 desc

Upvotes: 0

Aaron Rheams
Aaron Rheams

Reputation: 1

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.

Upvotes: 0

Unreason
Unreason

Reputation: 12704

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.

Upvotes: 2

Silver Light
Silver Light

Reputation: 45922

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')

Upvotes: 3

Related Questions