Update multiple sqlite rows with ids from same table

Question

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.

Please remove teh android-sqlite and mysql tags. This clearly has nothing to do with either of them. Please also explain where the MAINFORM numeric values come from and give the table structures. — e4c5
– e4c5, Commented Sep 9, 2015 at 2:57
Hi @e4c5. CL. removed tags (although it is for an Android app, so. :D ) But ok anyways. Also, maybe I was little unclear, but MAINFORM numeric values are found in the same table. As in value from MAINFORM can be found in table from column FORM. And numeric value is its ID. Little confusing, I know. But CL. go it in his answer. I just need to see if it will pass (and how long does it take) — dmestrovic
– dmestrovic, Commented Sep 9, 2015 at 10:11

CL. · Accepted Answer · 2015-09-09 08:21:27Z

2

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.)

answered Sep 9, 2015 at 8:21

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dmestrovic Over a year ago

How long will it take to finish this for 600.000 rows? I started query 10 minutes ago. Still going.

CL. Over a year ago

Well, an index on the Form column would have been useful …

dmestrovic Over a year ago

Yes. :) Had no time this morning to test it. I'm amazed by the speed of this. Thank you! Worked like a charm (4.8sec).

Collectives™ on Stack Overflow

Update multiple sqlite rows with ids from same table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related