I want to use the default value (string) for a specific column in a specific table in the where condition of an update statement in SQLite. So, the column should only be updated if the current value is not the default value. If that is the case, add a zero in front of it.
I tried the following:
WITH DefaultValueCTE AS (
SELECT dflt_value AS defaultValue
FROM pragma_table_info('Rate')
WHERE name = 'Nummer'
)
UPDATE Rate
SET Nummer = '0' || Nummer
WHERE Nummer NOT LIKE '0%'
-- I also tried NOT IN
AND Nummer <> (SELECT defaultValue From DefaultValueCTE LIMIT 1);
but it wont work. It updates the row although it shouldnt (Default value inserted) I also tried to use the sql statement directly, but nothing. When I directly use the default value ('Default') it works:
UPDATE Rate
SET Nummer = '0' || Nummer
WHERE Nummer NOT LIKE '0%'
AND Nummer <> 'Default';
Do you have any suggestions? The schema in CSV format
0,Id,char(20),1,,1
1,UpdateDate,TEXT,1,,0
2,CreateDate,TEXT,1,,0
3,Type,INTEGER,1,,0
4,Nummer,TEXT,1,'Default',0
Rhetorical; what does
SELECT dflt_value AS defaultValue
FROM pragma_table_info('Rate')
WHERE name = 'Nummer'
produce?
Answer NOT default
BUT 'default'
(enclosed in single quotes).
So an update along the lines of:-
UPDATE rate
SET nummer = '0'||nummer WHERE nummer NOT LIKE '0%' AND ''''||nummer||'''' NOT LIKE (SELECT * FROM defaultValueCTE);
Demo
The following is a working DEMO:-
DROP TABLE IF EXISTS rate;
CREATE TABLE IF NOT EXISTS rate (id char(20) PRIMARY KEY NOT NULL, updateDate TEXT NOT NULL, createDate TEXT NOT NULL, type INTEGER NOT NULL, nummer TEXT NOT NULL DEFAULT 'default');
SELECT *
FROM pragma_table_info('rate');
SELECT dflt_value AS defaultValue
FROM pragma_table_info('rate')
WHERE name LIKE 'Nummer';
WITH
cte_count(c) AS ( SELECT 1 UNION ALL SELECT c+1 FROM cte_count LIMIT 10)
INSERT INTO rate SELECT 'id'||abs(random()), datetime('now'), datetime('now'), abs(c % 5) + 1, 'blah' FROM cte_count;
WITH
cte_count(c) AS ( SELECT 1 UNION ALL SELECT c+1 FROM cte_count LIMIT 10)
INSERT INTO rate (id,updateDate,createDate,type) SELECT 'id'||abs(random()), datetime('now'), datetime('now'), abs(c % 5) + 1 FROM cte_count;
/* Show Data before any updates */
SELECT * FROM rate ORDER BY id;
/* First update */
WITH DefaultValueCTE AS (
SELECT dflt_value AS defaultValue
FROM pragma_table_info('rate')
WHERE name LIKE 'Nummer'
)
UPDATE rate
SET nummer = '0'||nummer WHERE nummer NOT LIKE '0%' AND ''''||nummer||'''' NOT LIKE (SELECT * FROM defaultValueCTE);
/* Result after first update */
SELECT * FROM rate ORDER BY id;
/* Update again to check changed values are not updated */
WITH DefaultValueCTE AS (
SELECT dflt_value AS defaultValue
FROM pragma_table_info('Rate')
WHERE name LIKE 'Nummer'
)
UPDATE rate
SET nummer = '0'||nummer WHERE nummer NOT LIKE '0%' AND ''''||nummer||'''' NOT LIKE (SELECT * FROM defaultValueCTE);
/* Result after 2nd Update */
SELECT * FROM rate ORDER BY id;
/* Cleanup test environment */
DROP TABLE IF EXISTS rate;
The result of the select prior to any updates:-
blah
)After the 1st update:-
i.e. the values that are not the default (default
) have been prefixed with 0 (to be 0blah
).
After the 2nd update to ensure that already updated values (i.e. with the 0 prefix) are not updated (prefixed again with another 0):-
The first and 2nd SELECTs confirm the value extracted via the pragma is enclosed:-
and :-