How to have the result of a select query being inserted as a value of an attribute in an insert stat-CodePudding

As mentioned in the title, I am trying to have a the result of a select query inserted in an insert statement, the code is:

INSERT INTO entry (
        event_id,
        entry_no, 
        entry_starttime, 
        entry_finishtime,
        comp_no,
        team_id,
        char_id
    ) VALUES ( 
        **SELECT event_id FROM event WHERE carn_date = to_date((SELECT carn_date FROM carnival WHERE carn_name = 'RM Autumn Series Caulfield 2022'), 'DD/MM/YYYY') and eventtype_code = '21K',**
        1,
        TO_DATE('08:31:44', 'HH24:MI:SS'),
        TO_DATE('09:02:44', 'HH24:MI:SS'),
        6,
        NULL, 
        NULL
    );

I am trying to have the result of SELECT event_id FROM event WHERE carn_date = to_date((SELECT carn_date FROM carnival WHERE carn_name = 'RM Autumn Series Caulfield 2022'), 'DD/MM/YYYY') and eventtype_code = '21K' being inserted as the value of event_id but it is giving me syntax error saying that 'missing expression'.

But when I run the select statement individually it perfectly gives out the desired result. Which is 14.

Thank you in advance for your help.

CodePudding user response：

You can use INSERT ... SELECT:

INSERT INTO entry (
  event_id,
  entry_no, 
  entry_starttime, 
  entry_finishtime,
  comp_no,
  team_id,
  char_id
)
SELECT event_id,
       1,
       TRUNC(carn_date)   INTERVAL '08:31:44' HOUR TO SECOND,
       TRUNC(carn_date)   INTERVAL '09:02:44' HOUR TO SECOND,
       6,
       NULL,
       NULL
FROM   event
WHERE  eventtype_code = '21K'
AND    carn_date = (SELECT carn_date
                    FROM   carnival
                    WHERE carn_name = 'RM Autumn Series Caulfield 2022');

You can also wrap your nested SELECT in () brackets; however, you will then get the default date components for year, month and day (which will be the first day of the current month and year) for the entry_starttime and entry_finishtime columns when you probably do not want that and instead want them to match the date of the carnival (which the first query does).

INSERT INTO entry (
  event_id,
  entry_no, 
  entry_starttime, 
  entry_finishtime,
  comp_no,
  team_id,
  char_id
) VALUES ( 
  ( SELECT event_id
    FROM   event
    WHERE  carn_date = ( SELECT carn_date
                         FROM   carnival
                         WHERE  carn_name = 'RM Autumn Series Caulfield 2022')
    AND    eventtype_code = '21K' ),
  1,
  TO_DATE('08:31:44', 'HH24:MI:SS'),
  TO_DATE('09:02:44', 'HH24:MI:SS'),
  6,
  NULL, 
  NULL
);

Note: Never use TO_DATE on a value that is already a DATE. If you want to truncate the time component of a DATE back to midnight then then use the TRUNC function rather than relying on an implicit cast to a string and then back to a date.

db<>fiddle here