Rauno Rauno - 3 months ago 13
SQL Question

SQL multiple insert procedure

I've been stuck all morning and I can't seem to figure out what the problem is here, could a kind soul help me out:

CREATE PROCEDURE translationvalue(
IN group1 CHAR(20),
IN ns1 CHAR(20),
IN title1 CHAR(20))
BEGIN
insert into vitanet__translationmarker
(site, `group`, ns, title, `type`)
values
(`cms`, group1, ns1, title1, `value`)
insert into vitanet__translation
(marker, locale, `value`)
select id, 'en', 'test'
from vitanet__translationmarker
where `group` = group1 and ns = ns1 and title = title1
insert into vitanet__translationjavascript
select id
from vitanet__translationmarker
where `group` = group1 and ns = ns1 and title = title1
END


Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'insert into vitanet__translation
(marker, locale, `value`)
selec' at line 10


Thanks!

Answer

I think you are using backticks, where you intend single quotes. In addition, you need to make sure you have a delimiter statement. Perhaps this will work:

DELIMITER $$
CREATE PROCEDURE translationvalue(
    IN in_group1 CHAR(20), 
    IN in_ns1 CHAR(20), 
    IN in_title1 CHAR(20)
)   
BEGIN
  insert into vitanet__translationmarker(site, `group`, ns, title, `type`) 
      values ('cms', in_group1, in_ns1, in_title1, 'value');

  insert into vitanet__translation (marker, locale, `value`) 
      select id, 'en', 'test' 
      from vitanet__translationmarker 
      where `group` = in_group1 and ns = in_ns1 and title = in_title1;

  insert into vitanet__translationjavascript(id)
      select id 
      from vitanet__translationmarker 
      where `group` = in_group1 and ns = in_ns1 and title = in_title1;
END$$
DELIMITER ;

Notes:

  • I changed the parameter names to have an in_ prefix. This helps distinguish them from columns in tables.
  • In the first values() statement, I replaced backticks with single quotes.
  • I added the delimiter statements and semicolons to the end of lines.
  • Naming a column group is a really bad idea, because that is a SQL keyword and a MySQL reserved word.
  • I added the id column explicitly to the last insert. Even if there is one column, I think it is still best practice to include the name of the column (I am guessing it is called id).