Kolja Aidam Kolja Aidam - 2 years ago 132
MySQL Question

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)

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 an default value of 16 -> COALESCE(vtmd.ID,16)
But this doesnt happen. The procedure finishes without any errors and no inserts has been made. It's my guess it's because the SELECT finds/returns no row?

Am I overlooking something or is this the wrong approach?

PS: The alias is there for readability and the fieldnames are encapsed by ` but i could not use this char in the code section.


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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download