CODEWITHSUNDEEP
mysql
Roger Dodger
Roger Dodger

Reputation: 987

mysql update table and return all updated records

I need to update a mysql table and then return a list of all the id's and names of the fields that were updated.Given my table structure:

********************    
tbl_books
********************
id - int(11)
bookname -varchar(100)
status - varchar(100)

Given tbl_books has following rows:

 id: 1
 bookname: 'Midsummer Night Dream'
 status: 'Active'

 id: 2
 bookname: 'Another book name'
 status: 'Inactive'

 id: 3
 bookname: 'So fine'
 status: 'Active'

I need to update all the rows that show status = 'Active', set status='Disabled' and return the id's and bookname's as the result. I know the first part of the update query:

 UPDATE tbl_books
 SET status = 'Disabled'
 WHERE status = 'Active'

How do I get all the values that were updated, so that my return result of updated rows would be like so:

id: 1
bookname: 'Midsummer Nights Dream'

id: 3
bookname: 'So fine'

Upvotes: 0

Views: 1636

Answers (1)

Shalvin
Shalvin

Reputation: 178

Actually there are many ways to do this, either use a cursor, select all the rows that needs to be updated and feed them in a variable, and simply as you use fetch the row to be updated one at a time, perform a select operation to get the desired result.

Or you can use a temp table, and simply insert in it all the id's that will be affected as a result of the update, hence this will be a simple select query from the table where all the status = 'Disabled', insert this result into the temp, perform the update, and then simply use the ID which are in the temp table to get the output of the desired results.

Take a read at this site: http://www.mysqltutorial.org/mysql-cursor/ It will surely help with the implementation should you choose to go with cursors.

Cheers :)

Upvotes: 1

Related Questions