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

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');

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 ;

Method 2

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()) ) 
;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0