CODEWITHSUNDEEP
sqlinsertmariadbprocedure
Rauno
Rauno

Reputation: 636

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 value(
    IN group1 CHAR(20), 
    IN ns1 CHAR(20), 
    IN title1 CHAR(20))   
BEGIN
  insert into translationmarker 
      (site, `group`, ns, title, `type`) 
      values 
      (`cms`, group1, ns1, title1, `value`)
  insert into translation 
      (marker, locale, `value`) 
      select id, 'en', 'test' 
      from translationmarker 
      where `group` = group1 and ns = ns1 and title = title1
  insert into translationjavascript 
      select id 
      from 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 translation 
      (marker, locale, `value`) 
      selec' at line 10

Thanks!

Upvotes: 0

Views: 39

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

Could you add the query terminator ; after the each INSERT block.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270723

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).

Upvotes: 1

Related Questions