CODEWITHSUNDEEP
sqlite
dmestrovic
dmestrovic

Reputation: 722

Update multiple sqlite rows with ids from same table

I need some help with improving my SQLite database.

Right now table looks something like this:

ID |FORM     |MAINFORM
----------------------
1  |form     |form
2  |formAB   |form
3  |formBC   |form
4  |sample   |sample
5  |sampleAB |sample
6  |sampleBC |sample

What is the best and fastest way to update data in MAINFORM column from string format to int format, as in, to ID of an FORM which I can get from same table?

In the end I should have something like:

ID |FORM     |MAINFORM
----------------------
1  |form     |1
2  |formAB   |1
3  |formBC   |1
4  |sample   |4
5  |sampleAB |4
6  |sampleBC |4

So in this example "form" has an ID = 1, so all rows with MAINFORM = "form" will be updated to MAINFORM = 1. MAINFORM can be empty, but if it's not, it will definitely have corresponding value in the table somewhere as a FORM (it will have ID).

Currently, there is around 600.000 rows in this table.

Upvotes: 0

Views: 873

Answers (1)

CL.
CL.

Reputation: 180060

You can use a correlated subquery to look up the new value:

UPDATE MyTable
SET MainForm = (SELECT ID
                FROM MyTable AS T2
                WHERE T2.Form = MyTable.MainForm);

(The subquery will result in a value of NULL if the lookup fails.)

Upvotes: 2

Related Questions