insert if not exists else just select in mysql

Question

I have one mysql table 'alfa' that will contain the primary key of another table 'beta' in one column. But if the entry in 'beta' can not be found I want to insert the value in 'beta' and use the new key in 'alfa'. Can I do this in one query somehow ?

I currently have:

INSERT INTO alfa SET c1=(SELECT id FROM beta WHERE name = 'john');

which works fine when 'john' exists in the table, but fails otherwise. So could I improve it to let the new name be inserted and selected if it is not already there ? id is auto_incremented.

I have tried to looking at IF but have not yet found out how to use IF outside the SELECT, is that possible ?

I know I can do it in several queries but I am talking with a remote database so could be nice to do it all at once.

For example the tables could have been created like this:

CREATE TABLE alfa (
  c1 int,
  PRIMARY KEY (c1)
)

CREATE TABLE beta (
  id int auto_increment,
  name varchar(255),
  PRIMARY KEY (id)
)

so alfa.c1 should refer to the beta.id values.

In short I want to do:

insert the id for john from the beta table into c1 in alfa, if john does not exist in beta then insert john into beta and insert the new auto incremented id for john into c1 in alfa.

Answer 1

I'll have a go, but bear in mind that coming from a Microsoft SQL background, and I'm not familiar with the exact structure of your tables, so some of the the SQL is probably a bit ropey.

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END

Hope this is of some help.