Question 21652 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql query to insert row with multiple conditions

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?

Tags:

4

Answers


  1. 0 
    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'
  )
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
  4. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search