Why doesn't my mysql code work

Question

I wrote this code and when I execute it it says I have a problem with mysql syntax near the update statement

set @s1 = (select if ((select count(*) from information_schema.columns where table_name='foo' and column_name='bar_id') > 0,
                      'select 1',
                      'alter table foo add column bar_id bigint; update foo set bar_id = baz_id;'));
prepare stmt from @s1;
execute stmt;
deallocate prepare stmt;

If I change my code to

set @s1 = (select if ((select count(*) from information_schema.columns where table_name='foo' and column_name='bar_id') > 0,
                      'select 1',
                      'alter table foo add column bar_id bigint;'));
prepare stmt from @s1;
execute stmt;
deallocate prepare stmt;
update foo set bar_id = baz_id;

then it works. but I want the update statement inside the if condition.

I cannot make this into a SP.

Error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update foo set bar_id = baz_id' at line 1

Try reading the error message. It normally says what's wrong. — Álvaro González, Jun 13 '17 at 15:05

bishop · Accepted Answer · 2017-06-13T15:34:47.447

In your first code block, you attempt to prepare a string that contains two SQL statements. Unfortunately, MySQL prepare / execute cannot have multiple statements.

If you can't use SP, I think I'd suggest doing this like so:

set @s1 = (select if ((select count(*) from information_schema.columns where table_name='foo' and column_name='bar_id') > 0,
                      'select 1',
                      concat('alter table foo add column bar_id bigint default ', baz_id)));

prepare stmt from @s1;
execute stmt;
deallocate prepare stmt;

alter table foo alter column bar_id drop default;

But, honestly, I'd suggest you minimize DDL changes as those can have unpredictable run-time behavior. In this case, that means adding the foo.bar_id out-of-band and just perform an update as needed.

score 0 · Answer 2 · answered Jun 13 '17 at 15:31

The problem is that MySQL's prepared statements do not support multi-statements.

If you want to script the database structure updates, easiest way is to use a procedure without dynamic SQL (you might want to check the table_schema as well when you are doing the changes).

create procedure sp_fix_structure()
begin

declare v_cnt int;

select count(*) into v_cnt
from information_schema.columns
where table_schema=database() and table_name='foo' and column_name='bar_id';

if (v_cnt=0) then
  alter table foo add column bar_id bigint;
  update foo set bar_id = baz_id;
end if;

end

Why doesn't my mysql code work

2 Answers2