If you use GNUCash for your personal accounts you may decide that you want to split out a particular group of transactions into a new account or a subaccount in your expenses.

Unfortunately there is no easy way to achieve this in the GUI and so you can either sit there manually clicking and changing them by hand or you can resort to using a bit of SQL to do the work for you.

First, it is likely you are using the default file format which is compressed XML, so the first step is to save a copy in the SQLite format. Don’t delete your old file though, just in case you royally mess things up.

Next step, download a copy of DB Browser for SQLite and install this on your machine, then open the file you just saved.

Navigate to the ‘Browse Data’ tab and look at the ‘accounts’ table, here you will see a list of all your accounts. You want to find the ‘guid’ of the account where your transactions currently live and also the one you want to transfer them to.

Next you want to go to the ‘Execute SQL’ tab and use the query below as an example. This will move any transactions in the old account with a description containing ‘Student Loan Payment’ into the new account.

You may want to test just the SELECT portion of this first before the update to ensure it returns all the items you expect and only those items.

UPDATE splits
SET account_guid = 'GUID OF NEW ACCOUNT'
WHERE 
    tx_guid IN (
        SELECT 
            t.guid
        FROM
            transactions AS t
        JOIN
            splits ON t.guid = splits.tx_guid
        WHERE 
            t.description LIKE '%Student Loan Payment%'
            AND splits.account_guid = 'GUID OF OLD ACCOUNT' 
    );
AND
	account_guid = 'GUID OF OLD ACCOUNT'