0

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.

mikro45
  • 63
  • 1
  • 1
  • 9

2 Answers2

1

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;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • It seems to be working, but it's also updating newest ones with theirs IDs leaving no NULL value in Replaced_by column. – mikro45 Jul 11 '18 at 10:26
  • So I copied select from `Replaced_by = ()` and added to the end as `and id != ()`. And now it's looking good. Thank you – mikro45 Jul 11 '18 at 10:48
0

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