MySql query to insert row with multiple conditions-CodePudding

I need to add a new row for all post_ids that equal specific condition. So there is a table with post_id, meta_key and meta_value

id	post_id	meta_key	meta_value
1	100	page-type	city
2	100	banner	"banner"
3	101	page-type	city
4	102	page-type	city
5	102	banner	"new banner
6	111	page-type	non-city
7	111	banner	"non-citybanner"

I need to add for post_id that has meta_key = "page-type" and meta_value = "city" while meta_key = "banner" does not exist. In this example I should have a new row

id	post_id	meta_key	meta_value
8	101	banner	"my new banner"

Of course the table contains about 200K such rows :) Any suggestions?

CodePudding user response：

INSERT INTO
  post (
    post_id,
    meta_key,
    meta_value
  )
SELECT
  post_id,
  'banner',
  '"my new banner"'
FROM
  your_meta_table
GROUP BY
  post_id
HAVING
  MAX(CASE WHEN meta_key = 'banner' THEN 1 ELSE 0 END) = 0
  AND
  MAX(CASE WHEN meta_key = 'page-type' AND meta_value = 'city' THEN 1 ELSE 0 END) = 1

Or, preferably...

INSERT INTO
  post (
    post_id,
    meta_key,
    meta_value
  )
SELECT
  post_id,
  'banner',
  '"my new banner"'
FROM
  your_posts_table
WHERE
  NOT EXISTS (
    SELECT *
      FROM your_meta_table
     WHERE post_id = your_posts_table.post_id
       AND meta_key = 'banner'
  )
  AND EXISTS (
    SELECT *
      FROM your_meta_table
     WHERE post_id = your_posts_table.post_id
       AND meta_key = 'page-type'
       AND meta_value = 'city'
  )

CodePudding user response：

In MySQL 8.3 you can:

select the all "post_id" values that satisfy meta_key = 'page_type' and meta_value = 'city',
subtract all ids that have banners with the EXCEPT operation.
apply the insertion

INSERT INTO tab(post_id, meta_key, meta_value)
SELECT post_id, 
       'banner' AS meta_key, 
       'new_banner' AS meta_value
FROM (SELECT DISTINCT post_id FROM tab WHERE meta_key = 'page-type' AND meta_value = 'city'
      EXCEPT
      SELECT DISTINCT post_id FROM tab WHERE meta_key = 'banner') missing_ids;

CodePudding user response：

You should do a Select into a temp-table to achieve this.

First, we select all rows with your condition:

SELECT (post_id) FROM [table] where meta_key = 'page-type' and meta_value='city' and 
post_id not in (SELECT post_id FROM [table] WHERE meta_key='page-type' and meta_value='banner')

this selection gives you a list of post_ids where no banner-entry exists

Now you can use the found post_ids to insert into your table the data you need:

INSERT INTO [table] (id, post_id, meta_key, meta_value) 

SELECT id, post_id, meta_key, meta_value FROM (
   SELECT null AS 'id', post_id, 'page-type', 'banner' 
         FROM [table] 
               WHERE meta_key = 'page-type' 
                     AND meta_value='city' 
                     AND post_id not in (
                          SELECT post_id FROM [table] 
                                 WHERE meta_key='page-type' AND meta_value='banner'
                          )
 ) as TEMP

CodePudding user response：

you can do as follows :

take also the case where if banner exist create page-type

INSERT into _metas (post_id, meta_key, meta_value)
select post_id, if(meta_key = 'page-type', 'banner', 'page-type'), if(meta_key = 'page-type', 'my banner', 'my city')
    from your_meta_table
    group by post_id
    having count(*) = 1;