CODEWITHSUNDEEP
sqlitesql-update
Madivad
Madivad

Reputation: 3337

Updating multiple rows in SQLite with relevant data from the same table

I have a database that I don't control the source of directly and results in errant '0' entries which mess up generated graphs with these drops to zero. I am able to manipulate the data after the fact and update that database.

It is acceptable that the last known good value can be used instead and so I am trying to make a general query that will remove all the zeros and populate it with the last known value.

Luckily, every entry includes the ID of the last entry and so it is a matter of simply looking back and grabbing it.

I have got very close to a final answer, but instead of updating with the last good value, it just uses the first value over and over again.

dummy data

CREATE TABLE tbl(id INT,r INT,oid INT);
INSERT INTO tbl VALUES(1,10,0);
INSERT INTO tbl VALUES(2,20,1);
INSERT INTO tbl VALUES(3,0,2);
INSERT INTO tbl VALUES(4,40,3);
INSERT INTO tbl VALUES(5,50,4);
INSERT INTO tbl VALUES(6,0,5);
INSERT INTO tbl VALUES(7,70,6);
INSERT INTO tbl VALUES(8,80,7);

SELECT * FROM tbl;

OUTPUT:
| id|  r |oid|
|---|----|---|
| 1 | 10 | 0 |
| 2 | 20 | 1 |
| 3 |  0 | 2 |  ** NEEDS FIXING
| 4 | 40 | 3 |
| 5 | 50 | 4 |
| 6 |  0 | 5 |  ** NEEDS UPDATE
| 7 | 70 | 6 |
| 8 | 80 | 7 |

I have worked several queries to get results around what I am after:

All zero entries:

SELECT * FROM tbl WHERE r = 0;

OUTPUT:
| id |  r | oid |
|----|----|-----|
|  3 |  0 |   2 |
|  6 |  0 |   5 |

Output only the those rows with the preceding good row

SELECT * FROM tbl WHERE A in (
  SELECT id FROM tbl WHERE r = 0
  UNION
  SELECT oid FROM tbl WHERE r = 0
  )

OUTPUT:
| id|  r |oid|
|---|----|---|
| 2 | 20 | 1 | 
| 3 |  0 | 2 |
| 5 | 50 | 4 |
| 6 |  0 | 5 |

Almost works

This is as close as I have got, it does change all the zero's, but it changes them all to the value of the first lookup

UPDATE tbl 
SET r = (SELECT r 
         FROM tbl 
         WHERE id in (SELECT oid
                    FROM tbl 
                    WHERE r = 0)
        ) WHERE r = 0 ;

OUTPUT:
| id|  r |oid|
|---|----|---|
| 1 | 10 | 0 |
| 2 | 20 | 1 |
| 3 | 20 | 2 |  ** GOOD
| 4 | 40 | 3 |
| 5 | 50 | 4 |
| 6 | 20 | 5 |  ** BAD, should be 50
| 7 | 70 | 6 |
| 8 | 80 | 7 |

If it helps, I created this fiddle here that I've been playing with: http://sqlfiddle.com/#!5/8afff/1

Upvotes: 1

Views: 555

Answers (1)

forpas
forpas

Reputation: 164089

For this sample data all you have to do is use the correct correlated subquery that returns the value of r from the row with id equal to the current oid in the WHERE clause:

UPDATE tbl AS t
SET r = (SELECT tt.r FROM tbl tt WHERE tt.id = t.oid)
WHERE t.r = 0;

See the demo.

Upvotes: 1

Related Questions