Batch SQL modifications

From refbase

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.

Contents 1 Batch search & replace actions 1.1 Examples 2 Batch deletion of records 2.1 Examples

Batch search & replace actions

The general SQL syntax for batch update actions is:

UPDATE [table name] SET [field name] = "whatever" WHERE [field name] = "whatever"

Examples

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.

Examples

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))