return the updated value after running the update query in mysql

Question

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.

no you cannot get it, after the update, the only thing that you can get are the affected rows, which is `1` — Kevin, Sep 22 '14 at 08:44

fancyPants · Accepted Answer · 2015-12-04T14:49:32.103

5

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.

edited Dec 04 '15 at 14:49

answered Sep 22 '14 at 08:42

fancyPants

50,732
33
89
96

score 2 · Answer 2 · answered Dec 04 '15 at 13:47

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

This is good information **in general**, but not really necessary in this case. Anyway, upvote. — fancyPants, Dec 04 '15 at 14:52

return the updated value after running the update query in mysql

2 Answers2

Linked