Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Insert Query Help, part2

Posted on 2016-10-05
6
66 Views
Last Modified: 2016-10-05
This is related to the schema/sample data already posted on this question:
here


I have 2 rows that need to be added to a table, `wp_posts` that have a count for the number of contacts for a given post_id.   One row will have a meta_key of 'contacts' and the meta_value will be the count.  The 2nd row meta_key will be '_contacts' and meta_value will always be 'field_5790aaada7333'.  The post_id will be the same for both rows.  So for example, using the sample data posted on the other question, the rows would look like this when finished:
post_id 	meta_key 	meta_value
3001 	contacts 	5
3001 	_contacts 	field_5790aaada7333
3003 	contacts 	1
3003 	_contacts 	field_5790aaada7333
3005 	contacts 	2
3005 	_contacts 	field_5790aaada7333
3008 	contacts 	4
3008 	_contacts 	field_5790aaada7333

Open in new window


The other issue with this is that the rows may exist for a given already, and if so only the first row need to be updated with the actual number of contacts.  My thought was to count the number of rows where the meta_key is like one of the meta_keys that is static, i.e. field_5790aac3a7334, for a given post_id, however I am unsure of how to either update the value of a row or insert 2 new rows if the rows do not exist.
0
Comment
Question by:bdhtechnology
  • 3
  • 3
6 Comments
 
LVL 1

Author Comment

by:bdhtechnology
ID: 41829700
Actually there are only 7 rows where the fields exist already, so the part regarding the update portion of the query can be ignored, I just need to add 2 rows for each post_id to count the number of contacts.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41829704
can this be a separate query?

I am about to finish up for the night, so thi s is as far as my sleepy eyes would let me go.. hope it helps

SELECT
...
  CASE
         WHEN cj.n = 1 THEN c.contact_count
         WHEN cj.n = 2 THEN 'field_5790aaada7333'
  END AS meta_value
FROM (
    SELECT
         cc.company
       , COUNT(*) contact_count
    FROM `chambercontacts` cc
    GROUP BY
         cc.company
      ) c
...
CROSS JOIN (
          SELECT 1 n UNION ALL
          SELECT 2 n
         ) cj
    

Open in new window

0
 
LVL 1

Author Comment

by:bdhtechnology
ID: 41829708
Yes, I have the rows inserted now but to use them I need to have the correct count for the actual number of contacts.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 41829717
please refresh page, my lat comment was being changed as you added :)

I'm off to bed I'm afraid way too tired
0
 
LVL 1

Author Comment

by:bdhtechnology
ID: 41830989
That was quite helpful, with a few tweaks I was able to finish this up.  Thanks for all of your help!

INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`)
SELECT
  p.ID AS post_id,
  CASE
         WHEN cj.n = 1 THEN 'contacts'
         WHEN cj.n = 2 THEN '_contacts'
  END AS meta_key,
  CASE
         WHEN cj.n = 1 THEN c.contact_count
         WHEN cj.n = 2 THEN 'field_5790aaada7333'
  END AS meta_value
FROM (
  SELECT
     cc.company,
     cc.deleted,
     COUNT(*) contact_count
  FROM `chambercontacts` cc
  WHERE cc.deleted=0
  GROUP BY
       cc.company
  ) c
  LEFT JOIN `directory` d ON c.company=d.id
  LEFT JOIN `wp_posts` p ON d.companyname LIKE p.post_title
  CROSS JOIN (
          SELECT 1 n UNION ALL
          SELECT 2 n
  ) cj
WHERE
  d.deleted=0 AND
  p.ID>=3000;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41831084
Great! Thanks for the question(s). Cheers, Paul
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question