I am attempting to do a column update in SQLite where if a certain string exists, update the column EXCEPT if it matches a particular string. Note, I can't use an EXCEPT statement in the select query combined with UPDATE as apparently SQLite doesn't allow that otherwise the select works fine with EXCEPT.
SELECT * FROM AUDIT_INVENTORY_20250307
--UPDATE AUDIT_INVENTORY_20250307
--SET NAMING_CONVENTION = 'Persistent draft, test or training items older than 60 days'
WHERE
LOWER(title) GLOB LOWER('*Test*')
OR LOWER(title) GLOB LOWER('*Testing*')
OR LOWER(title) GLOB LOWER('*Delete*')
OR LOWER(title) GLOB LOWER('*Untitled*')
OR LOWER(title) GLOB LOWER('*Draft*')
OR LOWER(title) GLOB LOWER('*Training*')
AND LOWER(title) NOT GLOB LOWER('*Shovel Test*')
AND created > '2025/01/07'
So I've got the update commented out while I test the select part of this statement but it continues to return strings that contain
Shovel Test
Which I specifically want to exclude from the update.
AND has precedence over OR, so you need to use parathesis to override the rule of precedence.
Ref : SO Answer
So based on above sample query would be
SELECT title FROM test
WHERE
(
LOWER(title) GLOB LOWER('*Test*')
OR LOWER(title) GLOB LOWER('*Testing*')
OR LOWER(title) GLOB LOWER('*Delete*')
OR LOWER(title) GLOB LOWER('*Untitled*')
OR LOWER(title) GLOB LOWER('*Draft*')
OR LOWER(title) GLOB LOWER('*Training*')
)
AND LOWER(title) NOT GLOB LOWER('*Shovel Test*')
OUTPUT
title
Test row 1
Testing row 2
Delete row 3
Untitled row 4
Draft row 5
Training row 6