Batch SQL modifications
This page shows how to perform batch changes using raw MySQL queries:
Important: Regular users are only permitted to execute SELECT queries via sql_search.php. Besides SELECT, the admin will be allowed to perform UPDATE, DELETE or INSERT queries which enable the admin to easily perform any batch changes on the database. While this is a very powerful feature, its easy to screw up thousands of records at once, so you should know what you're doing! If you're unsure about the effect of a particular SQL query, you may want to test your query on a non-production database first.
Batch search & replace actions
The general SQL syntax for batch update actions is:
UPDATE [table name] SET [field name] = "whatever" WHERE [field name] = "whatever"
Let's assume you've imported hundreds of records which have reference types that are unrecognized by refbase. In order to fix this, login as admin and execute a SQL query similar to the one below via sql_search.php or your MySQL command line interpreter. Here's an example that replaces all occurrences of reference type "Book" with "Book Whole":
UPDATE refs SET type = "Book Whole" WHERE type = "Book"
Here's another example that replaces all occurrences of a particular author name ("Steffens, Matthias") within the author field of all records with another string ("Steffens, M."):
UPDATE refs SET author = REPLACE(author, "Steffens, Matthias", "Steffens, M.") WHERE author RLIKE "Steffens, Matthias"
You can use following query syntax if you want to append a custom string to a particular field. These example queries add a string with the current date to the end of the notes field for ALL records in your database:
UPDATE refs SET notes = CONCAT("Imported from Endnote on ", CURDATE()) WHERE notes IS NULL OR notes = "" UPDATE refs SET notes = CONCAT(notes, ". Imported from Endnote on ", CURDATE()) WHERE notes RLIKE ".+"
Batch deletion of records
The general SQL syntax for deleting anything is:
DELETE FROM [table name] WHERE [field name] = "whatever"
PLEASE NOTE: Uploaded files (e.g. .pdf's) will not be deleted using this way. The admin should delete these files manually.
Purge the table refs:
DELETE FROM refs
Delete all records, where the author field contains "Simmonds":
DELETE FROM refs WHERE author RLIKE "Simmonds"
Delete all records, where the serial field is greater than 100:
DELETE FROM refs WHERE serial > 100
Delete all records that were added today:
DELETE FROM refs WHERE created_date = CURDATE()
Delete all records that were added yesterday:
DELETE FROM refs WHERE created_date = CURDATE()-1
Delete all records that were added during the last three days:
DELETE FROM refs WHERE created_date >= (DATE_SUB(NOW(), INTERVAL 3 DAY))