Update Null value

Question

This question reflects my issue. How to do this in SQLite?

I've tried UPDATE with self-joins, isolating the self join in sub-query, triggers, and something similar to this. Here is an example:

UPDATE stage 
SET title = 
(
  SELECT 
    prior.title
  FROM 
    stage prior,
    stage now
  WHERE 
    prior.rownum+1 = now.rownum
)
WHERE
title is null

score 4 · Accepted Answer · answered May 19 '12 at 16:11

4

Every table in SQLite has got a pseudo-column called rowid (which can be accessible under several different names: rowid, oid, _rowid_, unless those names are assigned to other, real, columns). The rowid column is essentially a unique row identifier and you can use it as a sort criterion and/or in conditions.

The following query demonstrates how your problem can be solved with the help of rowid:

UPDATE stage
SET title = (
  SELECT title
  FROM stage AS prev
  WHERE title IS NOT NULL AND prev.rowid < stage.rowid
  ORDER BY prev.rowid DESC
  LIMIT 1
)
WHERE title IS NULL

Here's a demo on SQL Fiddle.

You can read more about rowid in this SQLite manual.

answered May 19 '12 at 16:11

Andriy M

76,112
17
94
154

Thank you Andriy. I tried your query on Fiddle, and yes it did work. But when I tried it on my db it did not. I tried it three ways: once in SQL Manager Firefox Add-in, once in Navicat (a commercial product), and once in the sqlite3 command line shell. None of them updated the table. Any ideas? – Paulb May 19 '12 at 23:36
No ideas, sorry. My knowledge about SQLite is insufficient at the moment, and I would be happy to know the answer myself as well as to help you. – Andriy M May 20 '12 at 08:24
I didn't have any of the products you mentioned, but I have now installed the SQLite Manager Firefox add-on to see if I could do anything about my solution to make it work at least there. And guess what? I ran all the commands: created the table, inserted values, retrieved rows before the update, then updated the table using the exact same statement as in my answer and looked at the output of `SELECT *` again – and it worked. Since I am not a very experienced SQLite user, I can't really tell you what may have been the difference. The version of the add-on I've installed is 0.7.7, Firefox 12.0. – Andriy M May 20 '12 at 15:21

Gerardo Lima · Answer 2 · 2012-05-21T11:01:52.737

I presented a solution for the problem you referred (I successfully tested on SQL2008, SQLite3 and Oracle11g). I copied that solution below:

CREATE TABLE test(mysequence INT, mynumber INT);
INSERT INTO test VALUES(1, 3);
INSERT INTO test VALUES(2, NULL);
INSERT INTO test VALUES(3, 5);
INSERT INTO test VALUES(4, NULL);
INSERT INTO test VALUES(5, NULL);
INSERT INTO test VALUES(6, 2);
SELECT t1.mysequence, t1.mynumber AS ORIGINAL
, (
    SELECT t2.mynumber
    FROM test t2
    WHERE t2.mysequence = (
        SELECT MAX(t3.mysequence)
        FROM test t3
        WHERE t3.mysequence <= t1.mysequence
        AND mynumber IS NOT NULL
       )
) AS CALCULATED
FROM test t1;

-- below here it was only tested in SQLite3, but I believe it should
-- work on other DBMS since it uses standard/non-proprietary SQL
UPDATE test
SET mynumber = (
    SELECT t2.mynumber
    FROM test t2
    WHERE t2.mysequence = (
        SELECT MAX(t3.mysequence)
        FROM test t3
        WHERE t3.mysequence <= test.mysequence
        AND mynumber IS NOT NULL
       )
);

Thanks G. How would that translate into an UPDATE statement? — Paulb, May 19 '12 at 13:16
Very nice G. Very elegant, I already checked an answer.. but this one worked flawlessly also. I wish I could pick two answers! — Paulb, May 21 '12 at 21:49
But mine uses only standard SQL! :) Now serious: there's no problem using proprietary SQL extensions, but this might be a concern if you plan migrating database provider in the future. — Gerardo Lima, May 22 '12 at 09:19

Update Null value

2 Answers2