SQL query that will add a new row with values from another table-CodePudding

i need to write query that will insert a new row into cms_objects_channels but only within given category that is in WHERE clause , but VALUES need to be

id_objects has to be equal to ID from subquery
id_channels must be 8 ( static so it will be easy)
contract_date has to be 6 months from today
contract_amount has to be 1000 (static)
params is little tricky because its have to be "hotel_id=id_objects" like "hotel_id=123"
status has to be "new" (static)
hotel_id has to be same as id_objects
active has to be set to 1 (static)

I wrote somthing like this but it doesnt work , VALUES are only to help visualize

INSERT INTO hotres_panel.cms_objects_channels (  id_objects 
                                                ,id_channels 
                                                ,contract_date 
                                                ,contract_amount 
                                                ,params 
                                                ,status 
                                                ,hotel_id  
                                                ,active ) 
WHERE id_objects IN
( SELECT id 
  FROM hotres_panel.cms_objects where ( category_id = 175 OR 176 OR 217 OR 180 OR 178 OR 218 OR 196) 
  AND (active = 1) AND (test = 0) 
  AND contract_date >= CONCAT(CURDATE())) 
  VALUES ( '961', '8', '2022-08-20', '199','hotel_id=123' 'new', '961', '1');

CodePudding user response：

INSERT INTO hotres_panel.cms_objects_channels (id_objects, id_channels, contract_date, contract_amount, params, status, hotel_id, active) 
SELECT 961, 8, '2022-08-20', 199,'hotel_id=123', 'new', 961, 1
WHERE 961 IN ( SELECT id 
               FROM hotres_panel.cms_objects 
               where category_id IN (175, 176, 217, 180, 178, 218, 196) 
                 AND active = 1 
                 AND test = 0
                 AND contract_date >= CONCAT(CURDATE()) ) 
;

CodePudding user response：

Below query is to give you a hint , you can modify based on your needs. Based on:

I wrote somthing like this but it doesnt work , VALUES are only to help visualize

To build a dinamic query you need to select all the columns that you are using in your insert statement. As @Akina mentioned in the comments

INSERT .. VALUES does not allow WHERE clause, you should use INSERT .. SELECT.

    INSERT INTO hotres_panel.cms_objects_channels (  id_objects 
                                                ,id_channels 
                                                ,contract_date 
                                                ,contract_amount 
                                                ,params 
                                                ,status 
                                                ,hotel_id  
                                                ,active ) 

SELECT   a.id_objects 
        ,'8' 
        ,curdate() INTERVAL 6 MONTH 
        ,1000
        ,concat_ws(' ' ,'hotel_id=',a.id_objects)
        ,'new' 
        ,a.id_objects
        ,'1'  

FROM  (

     SELECT id as id_objects 
     FROM  cms_objects 
     WHERE id IN
     (   SELECT id  
         FROM hotres_panel.cms_objects where ( category_id = 175 OR 176 OR 217 OR 180 OR 178 OR 218 OR 196) 
         AND (active = 1) AND (test = 0) 
         AND contract_date >= CONCAT(CURDATE())  
      ) as a ;