Build SQL query to identify missing rows and update them with specific data

Question

I know I should show what I've tried so that I can get better help, but I don't even know where to start on this one, so there's nothing to show. I was doing well to formulate this into an intelligent question. I am using PHP and MySQL:

arpr_customs (`id`, `customfield_id`, `contact_id`, `stamp_create`, `stamp_update`, `field_value`)
arpr_contacts (`id`, `stamp_create`, `stamp_update`, `key`, `email_address`, `title`, `first_name`, `middle_name`, `last_name`, `full_name`, `format_preference`, `company`, `department`, `address_1`, `address_2`, `address_3`, `city`, `state`, `postal_code`, `country`, `alternative_email_address_1`, `alternative_email_address_2`, `alternative_email_address_3`, `phone_number_1`, `phone_number_2`, `phone_number_3`, `mobile_phone_number_1`, `mobile_phone_number_2`, `mobile_phone_number_3`, `fax_number_1`, `fax_number_2`, `fax_number_3`, `referer_id`, `schedule_pid`, `export_pid`, `import_subscribe_ip_address`, `import_subscribe_date_time`, `import_confirm_ip_address`, `import_confirm_date_time`, `import_customs`, `import_confirmed`)

Given these two tables, I need to identify contact_ids where there is no row in arpr_customs that contains that contact_id and customfield_id '3'. once identified, I need to insert into arpr_customs a new row for each of them where the "id" is allowed to auto-increment, The customfield_id is set to "3", the "contact_id" is set to the the found id. and field_value is set to "Susie"

(Susie represents the name of a sales person who will take ownership of all currently un-owned accounts.) (customfield_id "3" is Salesperson name.)

Edit: I'd have put this in a comment, but it's too big. I took spencer's advice and worked on a duplicate table. Here's what actually worked for me. Thanks @spencer

INSERT INTO testing_customs (`contact_id`,`stamp_create`,`stamp_update`,`customfield_id`,`field_value`) 
SELECT o.id AS contact_id, 1341602090 as stamp_create, 1341602090 as stamp_update, 3 AS customfield_id,'Susie' AS field_value
FROM arpr_contacts o LEFT JOIN testing_customs u ON u.contact_id = o.id
AND u.customfield_id = 3  WHERE u.id IS NULL 

Answer 1

You can do what you're looking to do with either a not exist, not in or left join + is null query.

So something like:

SELECT * FROM arpr_contacts
WHERE id NOT IN (SELECT contact_id FROM arpr_customs)

I'm not sure of the exact MySQL syntax.

Also please visit this link for explanation of differences between Not Exist/Not In/Left Join.

Answer 2

To find rows in arpr_contacts which have no "matching" rows in the arpr_customs table (assuming here that arpr_customs.contact_id is a foreign key that points to arpr_contacts.id

SELECT o.id
  FROM arpr_contacts o
  LEFT 
  JOIN arpr_customs u
    ON u.contact_id = o.id
       AND u.customfield_id = 3
 WHERE u.id IS NULL

That's a familiar "anti-join" pattern. Basically, the statement is saying get all rows from contacts, along with all matching rows from customs... and then throw out any rows that matched, so we are left with rows from contacts for which there was no match. (We're depending on having a column with a NOT NULL constraint in the customs table, so we can test whether we had a match or not. I'm assuming here that the id column is the primary key, which we know can never be null. (NOTE: the actual execution plan for this query is a little different than what I described, but conceptually, that's the end result of what's happening.)

As a (usually) slower alternative, you can also use a correlated subquery to get the same result:

SELECT o.id
  FROM arpr_contacts o
 WHERE NOT EXISTS (SELECT 1 FROM arpr_customs u 
                    WHERE u.contact_id = o.id 
                      AND u.customfield_id = 3)

Or, if you're going for horrible performance on really large tables, you can use a NOT IN predicate. (Just be careful here to avoid getting any NULL values in the list.)

SELECT o.id
  FROM arpr_contacts o
 WHERE o.id NOT IN 
       ( SELECT u.contact_id 
           FROM arpr_customs u
           WHERE u.contact_id IS NOT NULL
             AND u.customfield_id = 3
       )

---

That only partially answers your question.

I notice now, as I was reviewing, that I left out any predicate on that customfield_id column. FIXED

And it doesn't address inserting the "missing" rows...

You can grab whatever columns you need of the arpr_contacts table, and then supply values for the other columns, to do an INSERT ... SELECT ...

I'd recommend you run just the SELECT part, and then (if you aren't exactly sure), create a temporary table as a standin to accept the insert. (You can grab the output from a SHOW CREATE TABLE arpr_customs, and edit that (remove foreign key constraints, and change the table name, and use that as a target for your insert.)

INSERT INTO arpr_customs (`contact_id`,`customfield_id`,`field_value`)
SELECT o.id    AS contact_id
     , 3       AS customfield_id
     , 'Susie' AS field_value
  FROM arpr_contacts o
  LEFT
  JOIN arpr_customs u
    ON u.contact_id = o.id
       AND u.customfield_id = 3
 WHERE u.id IS NULL

If the id column in the arpr_customs table is defined as AUTO_INCREMENT, a value will automatically be assigned for it.

Answer 3

try something like

Insert arpr_customs(Customfield_id,contact_id,field_value)
Select '3',arpr_contacts.id,'Susie'
From arpr_contacts
outer join arpr_customs On arpr_customs.customfield_id = '3' 
          and arpr_customs.contact_id = arpr_contacts.id
where arpr_customs.contact_id is null

Basically its' an outer join of contacts and customs for type '3' with a where to filter out those where there is matching record, and then use those for the insert.

My Mysql isn't brill though, so you might have a syntax funny in there.

Answer 4

INSERT INTO arpr_customs (customfield_id,contact_id,field_value)
SELECT '3',arpr_contacts.id,'Susie'
FROM arpr_contacts LEFT JOIN arpr_customs
on arpr_customs.contact_id=apr_contacts.id
where arpr_customs.contact_id is null