See results of update query before executing it

[mds1256]

Hi

 

I think im going crazy! a while back i'm sure i remember reading that you could run a 'select update' query so you could see what the results would look like before actually running the query. I cannot rember how to do it so could you please point me in the right direction

[stephenworks]

Hi

 

I think im going crazy! a while back i'm sure i remember reading that you could run a 'select update' query so you could see what the results would look like before actually running the query. I cannot rember how to do it so could you please point me in the right direction

 

As far as I know, this is not possible. You may have mixed up SELECT UPDATE with UPDATE ... SELECT, which does something completely different.

 

UPDATE `users` SET `avatar` = (SELECT `defaults`.`avatar` FROM `defaults` WHERE `id`='1')

 

This would look for the column avatar on the table 'defaults' where the ID column is 1, and put the value of the 'avatar' column into every user on the 'users' table to the value from the defaults table.

[mds1256]

Hey

 

Thanks, think I may have been confused.

 

I found what i was trying to do e.g.

 

If i wanted to update a field's data within a table and replace part of a piece of data but see what it would look like first i would do the following before running an update script:

 

select replace(location, 'UK', 'London') from table

 

this would show me what the table would look like after the query has ran, then once im happy i can run the update script.

 

Thanks for the comments

[Mchl]

You might have been thinking about transactions (available for InnoDB engine), where you can execute a query, but need to COMMIT it if you want the changes to actually take place, or can ROLLBACK it when something's not right.

 

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html