Get record ID if exists, insert new if record doesn't exist 3

[Foada]

I am new to MySQL and I just cannot seem to get the answer I am looking for. Basically I have a table that contains events so the table is comprised of an autoincrement id and name field. I need to add records to the table as events occur and log the details of the events in a different table using the event id. If the event name exists in the table I need a stored proc/function to return the id field. If the name does not exist, the stored proc/function should insert the new event name and return the new event id.

pseudo code
Select ID From Events Where Name = sNewName
If Exists ID Then
   Return ID
Else
   Insert Into Events (Name) Values (sNewName)
   Return newID
End If

Every search I have done leads to "Insert on Duplicate Key Update" but that does not fit the bill as I have to look up the key or create a new one. Hopefully I explained my issue well enough. Thanks in advance for any input.

If you choose to battle wits with the witless be prepared to lose.

 

[r937]

is the name declared with a unique index? i.e., can you add the same name under two different ids?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Foada]

The name cannot be added under two different ids. EventX may happen and I need to check to see if it exists in the event table so the first time EventX happens I need to add it to the event table and get the ID for it. I will then use the ID to log additional data in the History table such as time and date. When EventX happens an hour later I need to just retrieve the ID so that I can log it to the History table. When EventY and EventZ happen for the first time they need to be entered in the event table and each assigned their own ids. Subsequent EventX, EventY, and EventZ ids will just need to be returned so that they can be logged to the History table. The bottom line of this problem is that my data is getting passed to me in a flat file form and I am trying to log the data in a relational format so I don't keep repeating data. Hopefully that clears things up.

If you choose to battle wits with the witless be prepared to lose.

 

[DonQuichote]

INSERT INTO Events(Name)
       VALUES(sNewName)
       ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID);

After that either issue the last_insert_id function for your library (C, PHP, etc) or select it with

SELECT LAST_INSERT_ID();

The construct if using LAST_INSERT_ID with a parameter to set it is explained in the manual, in the ON DUPLICATE KEY section.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[Foada]

DonQuichote,
Thanks for you response, but when I run the code it allows the sNewName to be inserted duplicate times. I was able to get this to work but I don't know if it is the best way to handle things.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetEventID`(IN sEventName VARCHAR(45), OUT iID INT)
BEGIN
  Select EventID From EventList Where sEventName = EventName Into iID;
  IF FOUND_ROWS() = 0 THEN
    Insert Into EventList (EventName) Value (sEventName);
    Set iId = LAST_INSERT_ID();
  END IF;
END

Any thoughts?

If you choose to battle wits with the witless be prepared to lose.

 

[r937]

since Name is unique, here's a foolproof solution

INSERT IGNORE INTO Events (Name) VALUES ( 'youneek' ) ;
SELECT id FROM Events WHERE Name = 'youneek' ;



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Foada]

R937,
Thanks for you post but I am still able to insert 'youneek' multiple times with that insert. What are your thoughs on the posted code that I have?

If you choose to battle wits with the witless be prepared to lose.

 

[r937]

did you declare the unique constraint?

ALTER TABLE Events ADD UNIQUE name_ux ( Name )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

Well, off course! If you don't define a uniqueness constraint, MySQL will happily insert as many rows with the same name as you want, and the INSERT .. ON DUPLICATE KEY won't ever do an update (and thereby set the LAST_INSERT_ID to an existing value). INSERT IGNORE won't work either in that case, as there is no uniqueness constraint to ignore.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[Foada]

Thank you both. I got things working. Sorry I forgot about the unique constraint.

If you choose to battle wits with the witless be prepared to lose.