Auto assign payee and category for Money Manager EX

DROP TABLE IF EXISTS temp.Settings;
DROP TABLE IF EXISTS temp.NotesToPayee;

/* SETTINGS: Edit start and end date to limit which transactions are affected (e.g. the ones you just downloaded). */
CREATE TEMPORARY TABLE temp.Settings ([startDate], [endDate], [accountName], [overwrite]);
INSERT INTO temp.Settings VALUES (
	'2023-01-01' --Start date
	,'2023-01-03' --End date
	,'%' --Account name. Must match exactly, or use '%' to match all accounts.
	,FALSE --Overwrite payee/category/subcategory even if they are already set to something other than "Unknown". Default is FALSE.
);

/* SETTINGS: Add the exact name of the payee from your database followed by text to find in the Notes column. Use the percent sign to match any number of characters (e.g. '%COSTCO WHSE%'). If an apostrophe is part of the name, use two in a row (e.g. 'Chili''s'). */
CREATE TEMPORARY TABLE temp.NotesToPayee (payeeName, notesMatch);
--Troubleshooting: find values with less than 2 terms: \('(.+)', *'(.+)'\)
INSERT INTO temp.NotesToPayee VALUES
	('Chili''s','CHILI''S%')
	,('Costco', '%COSTCO WHSE%')
	,('Costco Gas', '%COSTCO GAS%')
;

/* Update Database */
UPDATE CHECKINGACCOUNT_V1
SET
	PAYEEID = (SELECT PAYEEID FROM PAYEE_V1 WHERE PAYEENAME = (SELECT payeeName FROM NotesToPayee WHERE CHECKINGACCOUNT_V1.NOTES LIKE notesMatch)) --Assign payee if match found in Notes.
	,CATEGID = CASE WHEN --Only change category if set to overwrite or currently listed as unknown, and a default payee category is defined.
						(
							(
								(SELECT [overwrite] FROM Settings)
								OR (SELECT CATEGNAME FROM CATEGORY_V1 WHERE CATEGORY_V1.CATEGID = CHECKINGACCOUNT_V1.CATEGID) = 'Unknown'
							)
							AND (SELECT CATEGID FROM PAYEE_V1 WHERE PAYEENAME = (SELECT payeeName FROM NotesToPayee WHERE CHECKINGACCOUNT_V1.NOTES LIKE notesMatch)) > -1
						)
		THEN (SELECT CATEGID FROM PAYEE_V1 WHERE PAYEENAME = (SELECT payeeName FROM NotesToPayee WHERE CHECKINGACCOUNT_V1.NOTES LIKE notesMatch)) --Assign category based on payee default.
		WHEN ((SELECT [overwrite] FROM Settings) AND (SELECT CATEGID FROM PAYEE_V1 WHERE PAYEENAME = (SELECT payeeName FROM NotesToPayee WHERE CHECKINGACCOUNT_V1.NOTES LIKE notesMatch)) = -1) --Only change category if set to overwrite and no default category is defined
		THEN (SELECT CATEGID FROM CATEGORY_V1 WHERE CATEGNAME = 'Unknown') --Reset category to unknown
		ELSE CATEGID END
	--,SUBCATEGID = CASE WHEN ((SELECT [overwrite] FROM Settings) OR (SUBCATEGID = -1)) --Only change subcategory if set to overwrite or currently not set
	--	THEN (SELECT SUBCATEGID FROM PAYEE_V1 WHERE PAYEENAME = (SELECT payeeName FROM NotesToPayee WHERE NOTES LIKE notesMatch)) --assign subcategory based on payee default
	--	ELSE SUBCATEGID END --SUBCATEGID removed from MoneyManagerEx database in version 1.6.2
WHERE
	Notes LIKE (SELECT notesMatch FROM NotesToPayee WHERE CHECKINGACCOUNT_V1.NOTES LIKE notesMatch)
	AND
		(
			(SELECT [overwrite] FROM Settings)
			OR CHECKINGACCOUNT_V1.PAYEEID = (SELECT PAYEEID FROM PAYEE_V1 WHERE PAYEE_V1.PAYEENAME = 'Unknown')
		) --Only change payee if set to overwrite or currently listed as unknown
	AND TRANSDATE >= (SELECT [startDate] FROM Settings)
	AND TRANSDATE <= (SELECT [endDate] FROM Settings)
	AND ACCOUNTID IN (SELECT ACCOUNTLIST_V1.ACCOUNTID FROM ACCOUNTLIST_V1 WHERE ACCOUNTNAME LIKE (SELECT [accountName] FROM Settings))
;

/* View results */
SELECT
	TRANSDATE
	,TRANSAMOUNT
	,CHECKINGACCOUNT_V1.NOTES
	,PAYEENAME
	,CATEGNAME
	,ACCOUNTLIST_V1.ACCOUNTNAME
	,TRANSCODE
	,*
FROM
	CHECKINGACCOUNT_V1
	LEFT JOIN PAYEE_V1 on CHECKINGACCOUNT_V1.PAYEEID = PAYEE_V1.PAYEEID
	LEFT JOIN CATEGORY_V1 on CHECKINGACCOUNT_V1.CATEGID = CATEGORY_V1.CATEGID
	LEFT JOIN ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID = ACCOUNTLIST_V1.ACCOUNTID
	,Settings
WHERE
	TRANSDATE >= Settings.[startDate]
	AND TRANSDATE <= Settings.[endDate]
	AND ACCOUNTLIST_V1.ACCOUNTNAME LIKE Settings.[accountName]
	AND payeeName = 'Unknown'
ORDER BY 
	TRANSDATE
;