INSERT and SELECT on the same table in one SQL statement-CodePudding

I have the following SQL query :

insert into service_parameters (
    services_f_service_id,
    parameter_type_f_type_id,
    value)
values ( 
    (select distinct services_f_service_id from service_parameters where value = 'XXX'), 
    1, 
    '<url>');

MySQL is complaining with the following error:

SQL Error [1093] [HY000]: You can't specify target table 'service_parameters' for update in FROM clause

Is there a way to achieve this without spliting the SQL statment into 2 ?!

CodePudding user response：

INSERT INTO service_parameters (
    services_f_service_id,
    parameter_type_f_type_id,
    value)
SELECT DISTINCT services_f_service_id,
                1, 
                '<url>'
FROM service_parameters 
WHERE value = 'XXX';

CodePudding user response：

Your syntax is incorrect hence the error is coming

refer the link below for the correct syntax to achieve what you are trying to achieve

https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

CodePudding user response：

You can use a select command with no table here:

insert into service_parameters (
    services_f_service_id,
    parameter_type_f_type_id,
    value)
select
    (select distinct services_f_service_id from service_parameters where value = 'XXX'), 
    1, 
    '<url>';

;

CodePudding user response：

You have a syntax error at: select distinct services_f_service_id try this: insert into service_parameters (services_f_service_id, parameter_type_f_type_id, value) values ((select distinct(services_f_service_id) from service_parameters where value = 'XXX'), 1, '<url>');