mysql update statement with a variable in a procedure

Question

I createed a procedure which includes an update. There are two variables otherCategoryId and otherCategoryId.

When I try to run the procedure on command line it gives me an error like:

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

The update statement is like this:

abstract_type set parent_fk = otherCategoryId  where id = otherCategoryId;

UPDATE: My procedure's source code is:

CREATE PROCEDURE fixCommodityIPRTypes(IN typeClass VARCHAR(255), IN commodityClass VARCHAR(255),IN parrentFk VARCHAR(255))
BEGIN

    DECLARE otherCategoryId BIGINT;
    DECLARE wrongNoOfAccesses INT; 
    DECLARE wrongTypeId INT; 
    DECLARE loop_count INT  DEFAULT 1;
    DECLARE num_rows INT; 
    DECLARE nameOfType VARCHAR(1255);

    select otherCategoryId = pt.id from abstract_type pt  where pt.class = typeClass and pt.name = 'Other' and  pt.normalized = 1 and pt.normalize_link_fk is null;

    -- declare cursor for all other - specify types  with parent fk specified

    block_cursor: BEGIN
    DECLARE changeOSTypesCursor CURSOR FOR SELECT x.id, x.name, x.no_of_accesses from abstract_type as x  where class = typeClass and normalized = 0 and parent_fk = parrentFk;

    open changeOSTypesCursor;
    select FOUND_ROWS() into num_rows;

    update_loop:LOOP
         fetch  changeOSTypesCursor into wrongTypeId, nameOfType, wrongNoOfAccesses;
      -- for each distinct other-specify and parent the category called

      inside_loop: BEGIN
      DECLARE nrOfAccess BIGINT;
      DECLARE otherId BIGINT;
      select NO_OF_ACCESSES , Id from  abstract_type where class = typeClass and normalized = 0 and parent_fk = otherCategoryId and name = nameOfType into nrOfAccess, otherId ;    
      -- if there are no types with the same text as the current os type with parent category 'OTHER'
      -- then we just change the parent to be the category 'OTHER' 
      if nrOfAccess =0
        update abstract_type set parent_fk = otherCategoryId  where id = wrongTypeId;
      -- else we must set the no_of_accesses for the type with parent 'OTHER' = 
      -- current no_of_acceses + accesses from the type with specified parent,
      -- replace the foreign_key in commodities with the type with parent_other and 
      -- delete 
      else if nrOfAccess > 0 then
      begin
          update abstract_type set no_of_accesses = wrongNoOfAccesses + nrOfAccess where id = otherId;
          update commodity set goods_type_fk = otherId where class = commodityClass and goods_type_fk = wrongTypeId;
          delete from abstract_type where id = wrongTypeId;
      end   
      if num_rows <= loop_count then
         leave update_loop;
      end if
      end inside_loop;
    end LOOP;

    close changeOSTypesCursor;
    END block_cursor;
      -- update trademarks with parent specified and delete  the category of level 1
    update type_xref set ref_parents_fk = otherCategoryId  where  ref_parents_fk = parrentFk;
    delete from abstract_type where id = parrentFk;
END ##

You need to supply the procedure source code.

Radu M.
– Radu M.

2012-08-20 08:59:12 +00:00
Commented Aug 20, 2012 at 8:59 — Radu M.
– Radu M., Commented Aug 20, 2012 at 8:59

fancyPants · Accepted Answer · 2012-08-20 10:05:03Z

1

You're missing a THEN in there.

MySQL manual IF statement.

Change this

  if nrOfAccess =0
    update abstract_type set parent_fk = otherCategoryId  where ...

to

  if nrOfAccess =0 THEN
    update abstract_type set parent_fk = otherCategoryId  where ...

UPDATE:

CREATE PROCEDURE fixCommodityIPRTypes(IN typeClass VARCHAR(255), IN commodityClass VARCHAR(255),IN parrentFk VARCHAR(255))
BEGIN

    DECLARE otherCategoryId BIGINT;
    DECLARE wrongNoOfAccesses INT; 
    DECLARE wrongTypeId INT; 
    DECLARE loop_count INT  DEFAULT 1;
    DECLARE num_rows INT; 
    DECLARE nameOfType VARCHAR(1255);

    select otherCategoryId := pt.id from abstract_type pt  where pt.class = typeClass and pt.name = 'Other' and  pt.normalized = 1 and pt.normalize_link_fk is null;
    --here you want to use the assignment operator := rather than is_equal operator =

    block_cursor: BEGIN
    DECLARE changeOSTypesCursor CURSOR FOR SELECT x.id, x.name, x.no_of_accesses from abstract_type as x  where class = typeClass and normalized = 0 and parent_fk = parrentFk;

    open changeOSTypesCursor;
    select FOUND_ROWS() into num_rows;
    --FOUND_ROWS() will return a random number or zero or NULL here, since you need to do a query with SELECT SQL_CALC_FOUND_ROWS col1, col2 FROM ... first.

    --before you fetch anything from cursor, you want to declare a continue handler when there are no more rows found.
    --have a look at this example here: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

         fetch  changeOSTypesCursor into wrongTypeId, nameOfType, wrongNoOfAccesses;

      DECLARE nrOfAccess BIGINT;
      DECLARE otherId BIGINT;
      select NO_OF_ACCESSES , Id from  abstract_type where class = typeClass and normalized = 0 and parent_fk = otherCategoryId and name = nameOfType into nrOfAccess, otherId ;    

      if nrOfAccess =0 THEN
        update abstract_type set parent_fk = otherCategoryId  where id = wrongTypeId;
      else if nrOfAccess > 0 then
      begin
          update abstract_type set no_of_accesses = wrongNoOfAccesses + nrOfAccess where id = otherId;
          update commodity set goods_type_fk = otherId where class = commodityClass and goods_type_fk = wrongTypeId;
          delete from abstract_type where id = wrongTypeId;
      end IF;
      --you were missing an IF here. Please read proper syntax in the manual before posting questions. I'll leave the rest to you. We want to solve real problems here, you know? Not waste our time with syntax errors.

edited Aug 20, 2012 at 10:05

answered Aug 20, 2012 at 9:21

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tallica Over a year ago

i still when i try to run the code at line with end if of the last if so when i try to end the loop and inside_loop tag there show an syntax error and i cannot see why thanks

fancyPants Over a year ago

@tallica Updated my answer, removed your comments and replaced with mine.

tallica Over a year ago

i cannot have a continue handler because the inside select can return nothing so the cursor will stop before it's finish

fancyPants Over a year ago

@tallica Again, read the manual! dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Collectives™ on Stack Overflow

mysql update statement with a variable in a procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related