CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
deviloper
deviloper

Reputation: 7240

return the updated value after running the update query in mysql

Im sorry if the question seems odd and stupid but I need to know if it is possible or what.

So I have the following query that updates the status of a request record in the requests table. 

$updateQ = "UPDATE requests SET status = status+1 WHERE rid = '$rid'";
if($this->db->query($updateQ))
    return $this->db->select('status')->from('requests')->where('rid', $rid)->get()->row('status');

I wonder if I can get the value of the updated status field right after executing the query without running another select query.

Upvotes: 4

Views: 7486

Answers (2)

fancyPants
fancyPants

Reputation: 51868

No, you can't. And I don't see, why you worry about it. I'd guess, that rid is your primary key. The SELECT afterwards should therefore be so fast, that you'll hardly notice it.

Upvotes: 5

karmendra
karmendra

Reputation: 2243

You should use SELECT FOR UPDATE(for mySQL) or something similar to achieve atomicity in updating and selecting the last value.

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

or use LAST_INSERT_ID() if you wish to get last inserted id for your update.

UPDATE counters
SET value = LAST_INSERT_ID(value) + 1
WHERE id = 1;

And then

SELECT LAST_INSERT_ID();

Read thru select-update-single-statement-race-condition

Thanks, K

Upvotes: 2

Related Questions