bdhtechnology
asked on
SQL Insert Query Help, part2
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:
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.
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I have the rows inserted now but to use them I need to have the correct count for the actual number of contacts.
please refresh page, my lat comment was being changed as you added :)
I'm off to bed I'm afraid way too tired
I'm off to bed I'm afraid way too tired
ASKER
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;
Great! Thanks for the question(s). Cheers, Paul
ASKER