Conditional insert based on LAST_INSERT_ID

Question

A nearly identical question was asked here using an IF statement, but he didn't get an actionable answer, just suggested to go here where no IF statements are being used. I've tried to write both an IF statement and a conditional statement using the latter link but I'm stuck (see below).

I want to be able to conditionally insert a row only if the previous insert attempt actually inserted a row (ROW_COUNT > 0). The previous insert could have been duplicate data, so I'm deliberately setting it's LAST_INSERT_ID to null so no subsequent child inserts can occur with that LAST_INSERT_ID. The SQL script is created by a C# script, so it would be very possible that the LAST_INSERT_ID is not pointing to where you'd expect.

Here's a very small example of the script generated code (there are ~3 million rows in the final database):

SET @Vendors_Vendor_ID = (SELECT vendor_ID FROM VENDORS WHERE vendorName = 'PCA');
INSERT IGNORE INTO PCBID (PCBID, PCBDrawing, AssemblyDrawing, PONumber, Vendors_Vendor_ID) 
VALUES (11001, '10405', '41606', '091557.5', @Vendors_Vendor_ID);
SET @eventType_ID = (SELECT EVENTTYPE_ID FROM EVENTTYPES WHERE EVENTTYPE = 'Creation');
SET @USER = 'CTHOMAS';
INSERT IGNORE INTO EVENTS (PCBID, EVENTTYPE_ID, DATETIME, USER) 
VALUES (11001, @eventType_ID, '2009-06-15T13:15:27', @USER);
SET @EVENT_ID = IF(ROW_COUNT() > 0, LAST_INSERT_ID(), null);
-- THIS DOES NOT WORK
SELECT IF(@EVENT_ID != null, 
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
    VALUES (@EVENT_ID, 'Notes', 'WO#89574'), 
    null);

-- THIS DOESN'T WORK EITHER
INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT @EVENT_ID, 'Notes', 'WO#89574'
WHERE @EVENT_ID != null;

The PCBID table is not a problem for duplicate data, and the Events table has a composite unique key which prevents duplicate data by using INSERT IGNORE:

CREATE  TABLE IF NOT EXISTS `uniqueTest`.`events` (
`Event_ID` INT(11) NOT NULL AUTO_INCREMENT ,
`PCBID` INT(11) NOT NULL ,
`EventType_ID` INT(11) NOT NULL ,
`DateTime` DATETIME NOT NULL ,
`User` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`Event_ID`) ,
UNIQUE KEY `PDU_Index` (`PCBID`, `DateTime`, `User`),

The Problem: I need to be able to do a conditional insert based on the previous insert attempt into the Events table, if it was ignored (because it's duplicate data), don't insert any child rows either. There's currently no way to make any of the EventDetail data unique, there could be multiple rows of legitimate data based on a given Event_ID.

There are four levels deeper possible below the Events table depending on what type of data it is, if the event data doesn't get inserted because it's duplicate data, no child data gets written either (because it'll be duplicate as well).

You can use an AFTER INSERT trigger, and leave the ignore out. If you have a duplicate key entry the insert will fail and the trigger won't be executed. — Gervs, Sep 18 '14 at 21:17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Your second try was nearly right. You've got to check of NULL values with IS NOT NULL. So use

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT @EVENT_ID, 'Notes', 'WO#89574' FROM DUAL
WHERE @EVENT_ID IS NOT NULL;  -- instead of !=

or

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT t.* FROM (
    SELECT @EVENT_ID, 'Notes', 'WO#89574'
) t
WHERE @EVENT_ID IS NOT NULL;  -- instead of !=

The first one cannot work:

-- THIS DOES NOT WORK
SELECT IF(@EVENT_ID != null, 
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) ...

because the syntax of IF is

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

Conditional execution of statements is only possible in stored routines. The IF syntax of stored routines would allow something like

IF @EVENT_ID IS NOT NULL THEN
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) ...
END IF

You've got to distinguish those both syntax versions.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 18 '14 at 20:51

VMai

10,156
9
25
34

Thanks for the help, I appreciate it. I'm getting a: "Syntax error, unexpected WHERE, expecting END_OF_INPUT or ';'" on the WHERE clause using your example above. – delliottg Sep 18 '14 at 21:13
@delliottg My bad, I wasn't reading careful enough. If you're using a `WHERE` clause you've got to have a `FROM` clause too. You can use either `FROM DUAL`, borrowed from Oracle or use a subselect. Both pointed out in my edit. – VMai Sep 18 '14 at 21:20
No worries. I had to change the "a" just before the WHERE clause to a "t", which makes it work perfectly, thank you! Can you edit your answer with that? It won't let me make that small of a change. – delliottg Sep 18 '14 at 21:38
@delliottg is done. My test query did use the alias `a` but as I edited my answer I found `t` more convenient (short for temp) but didn't correct it thoroughly. It's better if such typos are fixed. I'm glad I could be of help and your problem is fixed. – VMai Sep 18 '14 at 21:40
Thanks again, you've saved me hours & hours, possibly days of work. – delliottg Sep 18 '14 at 21:41

Conditional insert based on LAST_INSERT_ID

1 Answers1

Linked