How to use the default value set for a table in a where condition in an update statement?

Question

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

Answer 1

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

• i.e. the default value is enclosed.

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

• i.e. a mix of default and other values (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 :-