1. DROP TEMPORARY TABLE IF EXISTS sa_with_external_nodes;
  2. CREATE TEMPORARY TABLE sa_with_external_nodes (PRIMARY KEY (site_area_id))
  3. SELECT DISTINCT sa.site_area_id FROM rabota_db.site_area sa
  4. JOIN site s ON sa.parent_id = s.site_id
  5. RIGHT JOIN rabota_db.site_area_demand_source_settings sds ON sa.site_area_id = sds.site_area_id
  6. WHERE sa.parent_id = 216564
  7. AND
  8. (
  9. sa.use_header_bidding = 1
  10. OR
  11. s.use_header_bidding = 1
  12. )
  13. AND sds.chain_status <> 1
  14. AND sds.status <> 1
  15. AND sds.deleted <> 0
  16. AND sds.end_date_ptz IS NOT NULL
  17. ;
  18.  
  19. SELECT * FROM sa_with_external_nodes;
  20.  
  21.  
  22. DROP TEMPORARY TABLE IF EXISTS sa_without_external_nodes;
  23. CREATE TEMPORARY TABLE sa_without_external_nodes (PRIMARY KEY (site_area_id))
  24. SELECT sa.site_area_id FROM rabota_db.site_area sa
  25. LEFT JOIN
  26.    sa_with_external_nodes saen ON saen.site_area_id = sa.site_area_id
  27. WHERE sa.parent_id = 216564
  28. AND saen.site_area_id IS NULL
  29. ;
  30.  
  31. SELECT * FROM sa_without_external_nodes;
  32.  
  33. INSERT INTO rabota_db.`site_area_demand_source_settings` (
  34.     `site_area_id`,
  35.     `demand_source_id`,
  36.     `status`,
  37.     `chain_id`,
  38.     `chain_type`,
  39.     `chain_status`,
  40.     `order`,
  41.     `manual_percent`,
  42.     `modify_date`,
  43.     `start_date_ptz`
  44. )
  45.     SELECT
  46.         sa.`site_area_id`,
  47.         2,
  48.         1,
  49.         1,
  50.         'manual',
  51.         1,
  52.         1,
  53.         100,
  54.         NOW() AS modify_date,
  55.         DATE(NOW()) AS start_date_ptz
  56.      
  57. FROM sa_without_external_nodes sa;