CODEWITHSUNDEEP
mysqlsql
Kolja Aidam
Kolja Aidam

Reputation: 41

INSERT INTO with SELECT .... what if SELECT returns 0 rows

I have a stored procedure with the following code:

INSERT INTO vt_benutzer_medien_liste (`BenutzerID`, `ReportID`, `MedienDateiTypID`, `Dateiname`, `Dateidatum`, `Dateigroesse`, `Bemerkungen`)
SELECT UserID, ReportID, COALESCE(vtmd.ID,16), Filename, FileDate, FileSize, Message 
FROM vt_medien_dateityp AS vtmd 
WHERE vtmd.Dateiendung = SUBSTRING_INDEX(Filename,'.',-1)
LIMIT 1

UserID, ReportID, Filename, FileDate, FileSize and Message are Parameters

The Insert part is working fine. The fileextension supplied in the parameter "Filename" is compared with a list of fileextensions in the table vtmd. This also is working fine, as long as the fileextension exists in vtmd.

If not, it should get a default value of 16 -> COALESCE(vtmd.ID,16) But this doesnt happen. The procedure finishes without any errors and no inserts has been made because the SELECT finds/returns no row.

What could I do?

PS: The alias is there for readability.

UPDATE

The SELECT return an empty result. This is the case where I want to return 16 as a default value.

Upvotes: 1

Views: 5964

Answers (2)

Kolja Aidam
Kolja Aidam

Reputation: 41

This works:

BEGIN
SELECT @XDateiTypID := COALESCE((SELECT ID FROM vt_medien_dateityp WHERE Dateiendung = SUBSTRING_INDEX(Filename,'.',-1) LIMIT 1),16);
INSERT INTO vt_benutzer_medien_liste (`BenutzerID`, `ReportID`, `MedienDateiTypID`, `Dateiname`, `Dateidatum`, `Dateigroesse`, `Bemerkungen`)
VALUES (UserID, ReportID, @XDateiTypID, Filename, FileDate, FileSize, Message);
END
  1. Handle the SELECT as a variable (@XDateiTypID in my case).
  2. If the variable stays NULL, it will get overwritten by using COALESCE with the default value (16 in my case).
  3. After that, just use the variable in the VALUES section and you're done.

PS: BEGIN .. END needed as it is a stored procedure.

Upvotes: 1

Mocas
Mocas

Reputation: 1660

If your SELECT returns no rows then nothing to INSERT. Having COALESCE won't force an INSERT as the SELECT has no rows, even if you specified a COALESCE for all the columns, this won't insert anything if no rows are returned from your SELECT .

There is a difference between null value in a returned row and no row at all.

Upvotes: 3

Related Questions