What I'm trying to do
So I'd like to get the id of the item. If it does not exist, then insert it and return it using LAST_INSERT_ID()
. So regardless of whether it exists or doesn't, I will have an id at the end.
Found an answer here, even though it's for sql-server: stackoverflow.com/a/8030455/6554121
Here's what I've tried by creating a procedure as follows:
CREATE PROCEDURE `find_map_items` (OUT o INT, IN b TEXT, IN n TEXT, IN q TEXT, IN m TEXT, IN t TEXT)
BEGIN
IF NOT EXISTS(
SELECT id
FROM item
WHERE
brand = b
AND
name = n
AND
quantity = q
AND
measurement = m
AND
type = t
)
BEGIN
INSERT INTO item (brand, name, quantity, measurement, type)
VALUES (b, n, q, m ,t);
SELECT LAST_INSERT_ID() INTO o
END
ELSE
BEGIN
SELECT id
FROM item
WHERE
brand = b
AND
name = n
AND
quantity = q
AND
measurement = m
AND
type = t
END
END
But it's giving me some red text and I'm not sure what the problem is:
I'm using MySQL Workbench.