mcmurphy mcmurphy - 1 month ago 10
MySQL Question

INSERT id from other column or NULL if value does not exist

THE DATA

I have a set of data in the following format:

CAR_MAKE TABLE
ID MAKE
1 Ford
2 Tesla
3 Acura
4 Honda
5 Toyota


MAKE_NOTES TABLE
NOTE_ID MAKE_ID MAKE_NAME NOTE
1 1 Ford New QNX-Based Sync System
2 2 Tesla Looking forward to Model 3
3 5 Toyota Updated Corolla 2018
4 Null Ferrari Very Fast and Very Red


I know there's repeating data (make_name) between table 1 and table 2. Let's assume I can't mess with the data. I also can't guarantee that an entry would have been made in car_make first. In such a case make_notes.make_id should be null.

WHAT I'VE DONE SO FAR

What I'm trying to do is INSERT a row into make_notes, inserting null into make_id if it does not exist in car_make, otherwise inserting car_make.id.

This works fine if, make_name exists in car_make... but if I attempt to insert a record with a make_name that does not exist in car_make, no record is inserted (no error is thrown either).

INSERT INTO make_notes (
make_id,
make_name,
note
)
SELECT
id,
'ford',
'New Note'
FROM car_make
WHERE make = 'ford';


I've also tried to use this as a subquery:

SELECT
CASE
WHEN (SELECT EXISTS (SELECT 1 FROM car_make where make = 'Ferrari') = 1)
THEN car_make.id
ELSE null
END AS make_id
FROM car_make;


I have not been albe intergrate it into my main query without throwing an error. As a stand alone query it returns one row for each entry in car_make, as null if 'Ferrari' does not exist, and each id if it does.

QUESTION

How do I create an insert query that will insert into 'make_notes', and insert 'null' if make_name does not exist in car_make, and insert car_make.id if make_name does exist?

Answer

I think using an IF() clause should work for your case:

INSERT INTO `make_notes` (
    make_id,
    make_name,
    note
)
VALUES(
    IF(
        ((SELECT COUNT(*) FROM `car_make` WHERE `make` = 'ford') > 0),
        (SELECT `id` FROM `car_make` WHERE `make` = 'ford'),
        NULL
    ),
    'ford',
    'New note'
);