0

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:

enter image description here

I'm using MySQL Workbench.

A. L
  • 11,695
  • 23
  • 85
  • 163
  • `IF NOT EXISTS` can not be used that way. It is not clear to me what you want to achieve, so offering an alternative is a bit hard. – Norbert Jul 10 '17 at 01:52
  • @NorbertvanNobelen Trying to make it so that I return the id of the item if it exists, otherwise insert it and return the id anyways. I'll update the question regarding what I want to do. – A. L Jul 10 '17 at 02:02
  • @NorbertvanNobelen I could probably use two statements instead like `SELECT id ...` and if it returns nothing then just run the `insert` statement and get the ID with the `LAST_INSERT_ID()` – A. L Jul 10 '17 at 02:06
  • So one thing, is that any procedure should only have one BEGIN and one END, which you have covered by the first BEGIN and the very last END, and then any procedure handling goes between those two words. Also, see this about exists/not exists queries: https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html – Paul T. Jul 10 '17 at 04:41
  • @PaulT. was following https://stackoverflow.com/a/8030455/6554121 hoping that it would work inside a procedure. But I'm working around by being lazy and doing an `INSERT IGNORE` then following it with a `SELECT` – A. L Jul 10 '17 at 04:46
  • Ok, but know that the tags to the question for that example link are for sql-server, not mysql, which is why others may be confused about your syntax. – Paul T. Jul 10 '17 at 04:48
  • @PaulT. which is why I asked this question since I couldn't find anything regarding mysql. Didn't want to ask too general of a question. – A. L Jul 10 '17 at 04:51
  • Ok, that's fine. However, at the same link mentioned previously (which is mysql's own documentation), you can also search for `create procedure syntax`. Then between those two (create procedure and the exists/not exists query), should help you get started. Post another comment, though, if more help may be needed. I'll check back tomorrow. (and update your question to match any new[er] changes) – Paul T. Jul 10 '17 at 04:56
  • @A.Lau How's it going ... any update on the effort? – Paul T. Jul 11 '17 at 02:10
  • @PaulT. Nah I got lazy and just did a `select`, it there were no results returned then I do the `insert` and do the `select` again – A. L Jul 11 '17 at 03:22

0 Answers0