[Archivesspace_Users_Group] Deleting duplicate commas - MySQL woes

[Blake Carver]

Maybe it's just the copy and paste has left you with those darn "fancy quotes", I brought up MySQL 8 here and these both worked, but after I made sure to put the regular ' in there because when I copied and pasted your message it had the curly things and both commands barfed with:

"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',’ FROM title)' at line 2"

It's not super obvious in there, but the single quotes ' are curved, and not a plain '


Here's what worked:


MySQL [archivesspace]> UPDATE archival_object SET display_string = replace(display_string, ',,', ',');
Query OK, 1 row affected (0.10 sec)
Rows matched: 18501  Changed: 1  Warnings: 0

MySQL [archivesspace]> UPDATE archival_object SET title = TRIM(TRAILING ',' FROM title);
Query OK, 5 rows affected (0.12 sec)
Rows matched: 18501  Changed: 5  Warnings: 0

Not sure how this will get formatted or reformatted from the mail programs, so you may need to do it again.

________________________________
From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of Danielle Butler <dbutler at cals.org>
Sent: Thursday, December 20, 2018 6:26:47 PM
To: Archivesspace Users Group
Subject: [Archivesspace_Users_Group] Deleting duplicate commas - MySQL woes


We’re trying to get rid of duplicate commas in our data in component descriptions. We manually included commas when we were using Archivists' Toolkit, and we migrated all of that data. Now we'd like to clean it up so that every description doesn't have two commas if we were to ever more to the PUI.


I was given a command for an older MySQL server version, and modified it using the MySQL Server v. 8 manual.

I believe this is the appropriate way to format the command, but I am getting syntax errors on the first command. I've scoured the manual and can't sort out what is causing the error.


Does anyone see an apparent issue with my syntax that I'm missing? Again, these commands are for v. 8.


REPLACE INTO archival_object VALUES (display_string , ',,', ',');

SELECT TRIM(TRAILING ',' FROM 'title');


Thanks for any advice you can give,


Danielle Butler, CA | Archivist

Butler Center for Arkansas Studies | Central Arkansas Library System

www.butlercenter.org<http://www.butlercenter.org/>

100 Rock Street

Little Rock, AR 72201

501-320-5724


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20181221/2495e739/attachment.html>