MySQL : Insert Statement to add a record in TABLE_1

A single insert statement to add a new record in TABLE_1 for which :

1. TABLE_1_ID value to be inserted as total count of records in TABLE_1 + 1.
2. COLUMN_NAME will have value as “ABC”.
3. TABLE_2_ID value should be picked from TABLE_2 based on value of COLUMN_NAME which is “ABC”.
4. TABLE_3_ID value should be picked from TABLE_3 based on value of COLUMN_NAME which is “ABC”.

All above specified statements has to be done using Single Insert Statement. Schemas of all 3 tables are below :

TABLE_1

COLUMN_NAME     COLUMN_TYPE     IS_NULLABLE     COLUMN_KEY     COLUMN_DEFAULT    
-----------    --------------  --------------  -------------   --------------                   
TABLE_1_ID      smallint(6)     NO              PRI            (null)                      
COLUMN_NAME     varchar(200)    YES                            (null)                      
TABLE_2_ID      smallint(6)     YES             MUL            (null)                      
TABLE_3_ID      smallint(6)     YES             MUL            (null)

TABLE_2

COLUMN_NAME     COLUMN_TYPE     IS_NULLABLE     COLUMN_KEY     COLUMN_DEFAULT         
--------------  --------------  --------------  -------------  -----------------   
TABLE_2_ID      smallint(6)     NO              PRI            (null)                      
COLUMN_NAME     varchar(20)     YES                            (null)       

TABLE_3

COLUMN_NAME     COLUMN_TYPE     IS_NULLABLE     COLUMN_KEY     COLUMN_DEFAULT         
--------------  --------------  --------------  -------------  -----------------   
TABLE_3_ID      smallint(6)     NO              PRI            (null)                      
COLUMN_NAME     varchar(20)     YES                            (null)   

Answer

INSERT INTO TABLE_1(TABLE_1_ID, COLUMN_NAME, TABLE_2_ID, TABLE_3_ID)
SELECT COUNT(*) + 1, 'ABC', (SELECT TABLE_2_ID FROM TABLE_2 WHERE COLUMN_NAME = 'ABC'), (SELECT TABLE_3_ID FROM TABLE_3 WHERE COLUMN_NAME = 'ABC') FROM TABLE_1