Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why doesn't this SELECT statment have a FROM?

<CFIF ListLen(SESSION.WHSurveyStruct.reasonString, ";") gt 0>
    <CFQUERY name="insertReasons" datasource="#REQUEST.dsn#">
        INSERT INTO TWelcomeHome_Reason
        (ReasonID, SubReasonID, SurveyID)
        SELECT #sanitize(ListFirst(SESSION.WHSurveyStruct.reasonString, ";"))#, #sanitize(getLatestSurveyID.SurveyID)# 
        <CFLOOP list="#sanitize(ListRest(SESSION.WHSurveyStruct.reasonString, ';'))#" index="thisReason" delimiters=";">
            UNION ALL
            SELECT #sanitize(thisReason)#, #sanitize(getLatestSurveyID.SurveyID)#
        </CFLOOP>

    </CFQUERY>

I'm trying to understand what this does. I'm confused with the loop, why don't the select statements have a FROM? Ok they are just scalars.

What about how there's one select statement on the outside of the loop and one on the inside? I sort of don't get the point on union all. And how come there are 3 columns being specified (ReasonID, SubReasonID, SurveyID) but in each select 2 values are given?

dumped:

struct

CACHED: false EXECUTIONTIME: 0 RECORDCOUNT: 8 SQL: INSERT INTO TWelcomeHome_Reason (ReasonID, SubReasonID, SurveyID) SELECT 6, 18, 245 UNION ALL
SELECT 6, 21, 245
UNION ALL
SELECT 6, 24, 245
UNION ALL
SELECT 3, 5, 245
UNION ALL
SELECT 3, 6, 245
UNION ALL
SELECT 3, 8, 245
UNION ALL
SELECT 3, 11, 245
UNION ALL
SELECT 3, 7, 245


like image 907
Celeritas Avatar asked Nov 29 '22 16:11

Celeritas


1 Answers

It looks like it is just SELECTing scalar values, not records from any table. So

INSERT INTO myTable
SELECT 'foo'
UNION ALL
SELECT 'bar'

will insert two records into myTable, foo and bar.

like image 101
Bort Avatar answered Dec 10 '22 05:12

Bort