2

An earlier data import in CiviCRM placed some member numbers into a custom field (member_number) instead of the more useful (external_id) field.

My (admittedly limited) SQL skills are way too rusty, but what I'm trying to do is:

  1. IF external_id field is empty,
  2. AND the contact_type is "Individual"
  3. THEN copy the data from member_number to external_id for the matching internal id number.

I've tried a few variations of this, with different errors:

INSERT INTO test_table (external_id)
SELECT member_number 
FROM member_info
INNER JOIN test_table
ON memberinfo.entity_id=test_table.id
WHERE test_table.external_id IS NULL AND test_table.contact_type = "Individual"

Do I even really need the INNER JOIN on this? And I know the WHERE statement usually refers to the table you're pulling from, not the one you're inserting to, but I can't remember the right way to do this.

briank
  • 43
  • 1
  • 4
  • What's the table structure for test_table? and what's the error you are getting? – ysrb Dec 06 '11 at 23:42
  • Oops, meant to include that. The test_table is a clone of the civicrm_contacts table, quite a few fields that are mostly varchar; The error is: #1062 - Duplicate entry '0' for key 'UI_external_identifier' – briank Dec 07 '11 at 00:55
  • Nevermind.... I finally tracked down the "duplicate entry" it was referring to, turns out there were 2 "0" values in with the NULLS. Don't know why the other errors popped up, but eventually got it to work. Thanks... – briank Dec 08 '11 at 17:18

1 Answers1

2
update test_table
set external_id = 
if(external_id = '' and contact_type = 'Individual', member_number,external_id)
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
  • This one didn't do the trick, but I like the simplicity and I'm sure this is on the right track. I'm still getting an error, "#1054 - Unknown column 'member_number' in 'field list'" – briank Dec 07 '11 at 01:04