CiviCRM Community Forums (archive)

I greatly appreciate others' input on how I could DELETE activities using their IDs in one command in the SQL command line of my database.  Now, I want to UPDATE activities.  Many activities have in their Subject Line, a code for topic. I created a Custom Multiple Choice field whose values match those codes.  So it could be a simple Batch Update, of making all Activities where Subject = "CN", now have a "CN" in that Custom field.

Only problem is, there are about 2500 Activities, and one can only Batch Update 100 activities at a time on the front-end.  Is there a way to do this in SQL that would save time?

(If Search Builder would allow me to get a list of Activities where the Custom field is Empty, that would help, but Search Builder can only return results as Contacts, not Activities (or any other record type), which is annoying.)

Thanks!

Thanks a bunch! Yeah with some Googling I was able to piece together the syntax, and successfully ran this command!:
below I call it customtable.field for clarity.

UPDATE customtable
INNER JOIN civicrm_activity ON (customtable.entity_id = civicrm_activity.id)
SET customtable.field = civicrm_activity.subject
WHERE customtable.entity_id IN ( a bunch of Activity IDs here to update )

I had to put in a WHERE statement, since the field is multiple choice, and while Subject lines have them separated by comma, space (e.g. "TA, HS"), customtable.field encodes a similar selection as  "TAHS".  So I was only able to update the 75% of Activities where only one Issue Area was selected. Is there any good way for the other Activities, to have the SQL database take the value in table 1, drop ", " and put it into table 2?  (it sees .subject = "TA, HS", and sets .field = "TAHS")