3 Types of MySQL Comments

mysql> SELECT 1+1; # This comment continues to the end of line

mysql> SELECT 1+1; -- This comment continues to the end of line

mysql> SELECT 1 /* this is an in-line comment */ + 1;

mysql> SELECT 1+ /*
this is a multiple-line comment
*/
1;

A Technique for Testing an Update Safely

Create a separate table
create table testtable as select * from realtable;

And execute the statement there.

In fact it wouldn't hurt to have an entire duplicate test database, which you can easily create with a data dump

Accidently destroying data with an UPDATE

At times the syntax of MySQL can appear overly strict eg when you are hurriedly hand typing a query and it keeps throwing up a syntax error. At other times however MySQL will output no error when you might wish it would. An example of this might be string concatenation

If you forgot that you needed to use Concat for this
ie

update tbl_password set url=concat('http://',url) where url not like
'http://%' limit 1; ### CORRECT###

but instead wrote

update tbl_password set url=url+'http://' limit 1; ### INCORRECT ###

You would destroy the contents of the field URL

Updating Data Strings with Concat

I needed to prefix http:// before any urls that didn't already have it.


update tbl_password set url=concat('http://',url) where url not like 'http://%';

(the following is my test statement which I used to avoid the risk of destroying data, by limiting it to one record)

update tbl_password set url=concat('http://',url) where url not like 'http://%' and url like '%supanames%';

Alternatively limit change to one record with 'limit'

update tbl_password set url=concat('http://',url) where url not like
'http://%' limit 1;