CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Update custom fields?
Pages: [1]

Author Topic: Update custom fields?  (Read 409 times)

vavroom

  • I post occasionally
  • **
  • Posts: 69
  • Karma: 4
    • Part of a Whole
  • CiviCRM version: 4.4.4
  • CMS version: Wordpress
  • MySQL version: 5.6.11
  • PHP version: 5.5.3
Update custom fields?
May 28, 2014, 03:56:43 pm
I need to update three custom fields for all of my 15,000 records. I thought batch update might help, but that's a limit of 100 at a time.

I found this: http://forum.civicrm.org/index.php/topic,31409.msg134595.html#msg134595 which got me thinking.

I am thinking that for my (admitedly limited) SQL skills, the easiest would be to do it in 2 goes - first append the id from the contact table into the custom field table, then run an update on these fields (all "yes"). I hit a snag.

Code: [Select]
INSERT INTO `civicrm_value_email_subscriptions_3` (entity_id) SELECT civicrm_contact.id from civicrm_contact;
This returns the following error:
Quote
Can't update table 'civicrm_contact' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Been googling but can't seem to find how to fix. There must be an easier way than this, surely?

Chris Burgess

  • Ask me questions
  • ****
  • Posts: 675
  • Karma: 59
Re: Update custom fields?
May 28, 2014, 05:38:07 pm
That's weird, I'd have thought you'd be able to do it like this ...
Code: [Select]
INSERT INTO civicrm_value_email_subscriptions (entity_id, customvalue_123) SELECT id, 'yes' FROM civicrm_contact;

... but it looks like you're hitting trigger errors preventing you referencing civicrm_contact in an INSERT ... SELECT so you need to use a temporary table.

Code: [Select]
CREATE TABLE tmp_contactids (id integer) SELECT id FROM civicrm_contact;
INSERT INTO civicrm_value_email_subscriptions (entity_id, customvalue_123) SELECT id, 'yes' FROM tmp_contactids;
DROP TABLE tmp_contactids;

I've been wondering if trigger logic is the future of CiviCRM.
@xurizaemon ● www.fuzion.co.nz

vavroom

  • I post occasionally
  • **
  • Posts: 69
  • Karma: 4
    • Part of a Whole
  • CiviCRM version: 4.4.4
  • CMS version: Wordpress
  • MySQL version: 5.6.11
  • PHP version: 5.5.3
Re: Update custom fields?
May 29, 2014, 07:18:24 am
You, sir, are a gentleman and a scholar! I owe you a beer next time we're in the same town! Thank you

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Update custom fields?

This forum was archived on 2017-11-26.