CODEWITHSUNDEEP
mysqlstored-proceduresinsert-into
monchi allarey
monchi allarey

Reputation: 13

IF NOT EXIST and Insert mysql stored procedure

hey guys can someone point me whats wrong in my mysql code im trying to create a stored procedure called in a trigger were a user insert a new book in the books table, the bookqty table insert if callNumber does not exist and updates when exist, but in some point the insert query is not working but the update query works fine thank you in advance

use librarydb;
drop procedure if exists intoBooksQty;
delimiter $$

    create procedure intoBooksQty(in newcallNumber varchar(10))

        begin
            if not exists (select * from books where callNumber = newcallNumber) then
                insert into librarydb.bookqty(callNumber,bookQty,bookqtyOut) values (newcallNumber, 1,0);
            else
                update bookqty set bookQty = bookQty + 1 where callNumber = newCallNumber;
            end if;
        end$$

delimiter ;

Upvotes: 1

Views: 8698

Answers (1)

wchiquito
wchiquito

Reputation: 16559

One option is to make a UPSERT.

DELIMITER //

CREATE PROCEDURE `intoBooksQty`(`newcallNumber` VARCHAR(10))
BEGIN
  INSERT INTO `bookqty` (`callNumber`, `bookQty`, `bookqtyOut`)
  SELECT `newcallNumber`, 1, 0
  FROM `books`
  WHERE `callNumber` = `newcallNumber`
    ON DUPLICATE KEY UPDATE `bookQty` = `bookQty` + 1;
END//

DELIMITER ;

SQL Fiddle demo

Upvotes: 1

Related Questions