Re-runnable mysql upgrade script

Question

Is there a way in mysql to write a re-runnable sql script? I tried the : 'IF EXISTS' syntax but that only seems to work for stored procedures. It doesn't seem the 'select if' syntax will do what I want.

Ideally I would like to do something like:

IF NOT EXISTS (select * from table where name='Name') Then insert into table values('Name');
else select 'Name has already been inserted into table';
end if;

Thanks.

Answer 1

Maybe you could store your database schema revision in suitable table and use that as a criteria for update processing?

Answer 2

We use a construct like (SQL Server)

IF 0 = (SELECT COUNT(*) FROM <your table> WHERE <your condition>)
BEGIN
    <your work here>
END

to manage SQL sections we want repeatable. If the script is re-run and the values are there, the count(*) is not zero and it doesn't do the work again.

Answer 3

You might be able to use:

INSERT IGNORE INTO t1 ...

You can see from the return whether a row was inserted or not. Note that it requires that there is a unique constraint, otherwise you will just get duplicates. If you have a primary key this is also a unique constraint.

Answer 4

insert into myTable(name)
select 'some_name_value' from myTable
where not exists (select 1 from myTable where name = 'some_name_value')
limit 1;

would insert value(s) if they don't already exist.