How to make a "for each" on Sqlite

Question

I have an A table with n records. For each record of this table, I want to get information from a field x to make a select and, based on the results of select, make a insert in a table B. Only I need to do this for all n records of A.

How do I do this in Sqlite? Is there anything similar to a "for each" for this situation? Or can I just do this operation for an application?

Answer 1

Sqlite is a database and not a programming language, therefore declarative query language that has in it allows something similar that is the select you already know. The for each is typically imperative.

It is possible to extend Sqlite to accept other things. It is open source and even has some facilities to include customizations. So you could create a kind of stored Procedure or SQL extension contained in it. Of course, it’s not something simple to do. It could be in C, a proper language, a standard SQL change or another language, such as Lua, C#, or another already known, each with its advantages and disadvantages.

So it is easier to do the query in a simple way and then manipulate in the application. That’s why we understand well everything that can be done in a query can help filter only what you want. I see many bad code queries out there, including from experienced people. Partly because the SQL query model doesn’t help, so many go to Nosql, but often for wrong reasons.

Having said all this it is possible to make a insert based on a select, that is, the data source of the insert may be the result of a select. Since the question doesn’t say what you need, I couldn’t help you anymore. Example:

INSERT INTO b (meuX) VALUES (x)
    SELECT x FROM a;

I put in the Github for future reference.