CODEWITHSUNDEEP
sqlsqlitesql-update
mikro45
mikro45

Reputation: 63

SQLite- Update old value based on the new value

I've got a following table with following data:

------------------------------------------
|ID|Name|Value|State |Version|Replaced_by|
------------------------------------------
|1 |Joe |10   |New   |1      |NULL       |
------------------------------------------
|2 |Ben |15   |New   |1      |NULL       |
------------------------------------------

Then I add for example 2 new rows:

------------------------------------------
|ID|Name|Value|State |Version|Replaced_by|
------------------------------------------
|1 |Joe |10   |New   |1      |NULL       |
------------------------------------------
|2 |Ben |15   |New   |1      |NULL       |
------------------------------------------
|3 |Joe |20   |New   |2      |NULL       |
------------------------------------------
|4 |Dan |10   |New   |1      |NULL       |
------------------------------------------

And then I want to perform an update where I update Replaced_by value of the oldest Version for group of Name and State, with ID value of the newest Version for group of Name and State, so it looks like this:

------------------------------------------
|ID|Name|Value|State |Version|Replaced_by|
------------------------------------------
|1 |Joe |10   |New   |1      |3          |
------------------------------------------
|2 |Ben |15   |New   |1      |NULL       |
------------------------------------------
|3 |Joe |20   |New   |2      |NULL       |
------------------------------------------
|4 |Dan |10   |New   |1      |NULL       |
------------------------------------------

And while I found a good looking answer to my problem, it is not working in SQLite. I got inspired by this answer in another question.

UPDATE
  table1
SET
  replaced_by = i.id
FROM
  (
    SELECT
      id, name, state, version
    FROM
      table1 t1
    WHERE
      version = (
        SELECT
          MAX(version)
        FROM
          table1
        WHERE
          t1.name = table1.name
          AND t1.state = table1.state
          AND t1.replaced_by IS NULL
      )
  ) i
WHERE
  table1.name = i.name
  AND table1.state = i.state
  AND table1.version = i.version
  AND table1.replaced_by IS NULL

Basically column Replaced_by is used as a additional versioning tool and to ease my life when selecting data from table.

Looking forward to any help.

Upvotes: 0

Views: 1546

Answers (2)

user10007778
user10007778

Reputation:

have you considered inserting each data one by one and you can use:

update (table name) set Name = 'Dan' where ID = 2

update (table name) set value = '20' where ID = 1

update (table name) set version = '1' where ID = 1

update (table name) set replaced_by = '3' where ID = 1

do this for all of your updates

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use exists with update statement :

update table
  set Replaced_by = (select max(t1.id) 
                     from table t1 
                     where t1.name = table.name and 
                           t1.State = table.State
                    )
where exists (select 1 
              from table t1 
              where t1.name = table.name and 
                    t1.State = table.State and 
                    t1.id <> table.id
              ) and replaced_by IS NULL;

Upvotes: 1

Related Questions