Hi ALL, I would like to insert a blank row or a new row with just a character or symbol in place of missing rowID's

So for instance, I have currently got:

RowID

1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 2 xxxxxxxxxxxxx 3 xxxxxxxxxxxxxxx 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxx 6 xxxxxxxxxxxxxxxx 9 xxxxx 10 xxxxxxxxxxxxx 11 xxxxxxxxxxxx 12 xxxxx 19 xxxxxxxxxxxxxxxxxxxxxxxxxx 20 xxxxxxxxxxx 21 xxxxxxxxxxxxxxxxxxxxxxxx

and I would like to create a table formatted like this

1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 2 xxxxxxxxxxxxx 3 xxxxxxxxxxxxxxx 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxx 5 (deleted) 6 xxxxxxxxxxxxxxxx 7 (Deleted) 8 (Deleted) 9 xxxxx 10 xxxxxxxxxxxxx 11 xxxxxxxxxxxx 13 (Deleted) 12 xxxxx 13 (Deleted) 14 (Deleted) 15 (Deleted) 16 (Deleted) 17 (Deleted) 18 (Deleted) 19 xxxxxxxxxxxxxxxxxxxxxxxxxx 20 xxxxxxxxxxx 21 xxxxxxxxxxxxxxxxxxxxxxxx

The database is a fairly large sms.db (110mb) and the table is the "messages" table.

I have tried putting together query's to achieve this but keep falling down with them!

Anyone able to make light work of what I have been banging my head against a wall with for days?

Thankyou!

CREATE TABLE t(id INTEGER PRIMARY KEY, message);

-- generate odd ids
INSERT INTO t
SELECT value, 'x'
  FROM generate_series(1, 10, 2);

-- fill gaps
INSERT INTO t
SELECT value, 'deleted'
  FROM generate_series((SELECT Min(id) FROM t), (SELECT Max(id) FROM t))
 WHERE NOT EXISTS (SELECT * FROM t WHERE id = value);

.mode box
SELECT *
  FROM t;
┌────┬─────────┐
│ id │ message │
├────┼─────────┤
│ 1  │ x       │
│ 2  │ deleted │
│ 3  │ x       │
│ 4  │ deleted │
│ 5  │ x       │
│ 6  │ deleted │
│ 7  │ x       │
│ 8  │ deleted │
│ 9  │ x       │
└────┴─────────┘

Thankyou

You want to generate a series and then use a LEFT JOIN to fill in the blanks.

DROP TABLE IF EXISTS v;
CREATE TABLE v
(
        id    INTEGER PRIMARY KEY NOT NULL,
        value TEXT
);

INSERT INTO v VALUES
        ( 1, 'a'),
        ( 5, 'e'),
        ( 6, 'f'),
        (10, 'j');


SELECT
  s.value AS id,
  ifnull(v.value, '(DELETED)') AS value
FROM
  generate_series(0, (SELECT max(id) FROM v)) AS s
LEFT JOIN
  v
  ON (s.value = v.id)
ORDER BY 1;

That will generate output that looks like this:

id  value    
--  ---------
0   (DELETED)
1   a        
2   (DELETED)
3   (DELETED)
4   (DELETED)
5   e        
6   f        
7   (DELETED)
8   (DELETED)
9   (DELETED)
10  j        

The table valued function generate_series() should be in the shell, but may not be in the core library. See https://www.sqlite.org/series.html

Thankyou

And on the off chance you don't have a version with generate_series(), you can achieve the same using a CTE - like this example shamelessly plagiarising Jay's excellent example, but using the CTE in stead:

DROP TABLE IF EXISTS v;

CREATE TABLE v (
    id    INTEGER PRIMARY KEY NOT NULL,
    value TEXT
);

INSERT INTO v VALUES
 ( 1, 'a')
,( 5, 'e')
,( 6, 'f')
,(10, 'j')
;

WITH GS(idx, maxid) AS (
    SELECT 0, (SELECT max(id) FROM v)
    UNION ALL
    SELECT idx + 1, maxid FROM GS WHERE idx < maxid
)
SELECT GS.idx,
       ifnull(v.value, '(DELETED)') AS value
  FROM GS
  LEFT JOIN v ON GS.idx = v.id
 ORDER BY 1;

  --  idx|value      
  -- ----|-----------
  --   0 |(DELETED)  
  --   1 |a          
  --   2 |(DELETED)  
  --   3 |(DELETED)  
  --   4 |(DELETED)  
  --   5 |e          
  --   6 |f          
  --   7 |(DELETED)  
  --   8 |(DELETED)  
  --   9 |(DELETED)  
  --  10 |j          

thankyou