0

In MySql, without using stored procedures or additional queries, how can I get a value from the affected row when using update?

Eg:

Table

id      |   item    |   status
-------------------------------
1       |   item-1  |   0
2       |   item-2  |   0
3       |   item-3  |   0
4       |   item-4  |   0
5       |   item-5  |   0
6       |   item-6  |   0

update items set status = 1 where id = 2;

//get value of `item` of affected row, in this case `item-2` since we updated `id = 2`

Is something like this available in MySql? Or at least another way to do it?

jmenezes
  • 1,888
  • 6
  • 28
  • 44
  • This might help you: http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql. – Gordon Linoff Oct 07 '14 at 11:39
  • at the end of your update query add something like: **SELECT item** – jbutler483 Oct 07 '14 at 11:41
  • That get's the id. Suppose I wanted another columns values, like item in the question, how do I do that? – jmenezes Oct 07 '14 at 11:42
  • If you have the id, like Gordon hinted, what issue do you have in performing a select using that id and selecting the column you need? In other words, no, you can't have "automatic" or magic column value retrieval after update. You can get the id, then perform a select and get what you need. Imagine if you updated a database with 1 billion records. Is it optimal to obtain the whole billion-record data set just so the "magic" could work? – N.B. Oct 07 '14 at 11:46
  • @N.B. It'll be just one unique row every time. – jmenezes Oct 07 '14 at 11:48
  • Retrieve the ID, use it to fetch the columns you need for your work. That's the only way. There's no magic available for what you want. Reason is what I outlined in comment before. – N.B. Oct 07 '14 at 11:49

0 Answers0