A single insert statement to add a new record in TABLE_1 for which :
- TABLE_1_ID value to be inserted as total count of records in TABLE_1 + 1.
- COLUMN_NAME will have value as “ABC”.
- TABLE_2_ID value should be picked from TABLE_2 based on value of COLUMN_NAME which is “ABC”.
- 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)
Advertisement
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