Using multiple columns from a nested SELECT in an INSERT query in sqlite3 [duplicate]

Question

I have to write an INSERT query in sqlite3 that uses the values of multiple columns from the same nested query.

In the example below i need to insert into table_a multiple values from the same nested query on table_b. I did not find a way to use the values from multiple columns without repeating the query.

This is what i am using at the moment:

INSERT INTO table_a (col_0, col_1, col_2, col_3) VALUES (
    ?,
    ?,
    -- Use different columns from the same query
    (SELECT col_x from table_b where rowid = ?),
    (SELECT col_y from table_b where rowid = ?)
);

As you can see, i am writing the query again and the output of EXPLAIN QUERY PLAN (below) shows that the nested query is in fact being executed twice. This is a very simple query but with more complex ones it would get very bad very quickly.

QUERY PLAN
|--SCALAR SUBQUERY 1
|  `--SEARCH table_b USING INTEGER PRIMARY KEY (rowid=?)
`--SCALAR SUBQUERY 2
   `--SEARCH table_b USING INTEGER PRIMARY KEY (rowid=?)

Is there an easier method for the simpler case where i am inserting values from the same table in the same columns? (In other words, duplicating an already existing row but with some columns modified)

INSERT INTO table_a (col_0, col_1, col_2, col_3) VALUES (
    ?,
    ?,
    -- Use different columns from the same query
    (SELECT col_2 from table_a where rowid = ?),
    (SELECT col_3 from table_a where rowid = ?)
);

Sorry, sir, but may be INSERT INTO (col_0, col_1, col_2, col_3) SELECT col_x,col_y,col_z,col_aa from table_b where rowid = ? — Sergey
– Sergey, Commented Jan 29 at 19:17
If parameter #3 and #4 always have the same value, then you can combine them in to a single fetch. However, if they can have different values, then the engine can't avoid doing two fetches. — The Impaler
– The Impaler, Commented Jan 29 at 19:26

samhita · Accepted Answer · 2025-01-29 19:17:31Z

0

You can combine the select into a single query

INSERT INTO table_a ( col_0, col_1 , col_2, col_3) 
SELECT  rowid, rowid,  col_x, col_y 
FROM table_b 
WHERE rowid = 1;

answered Jan 29 at 19:17

samhita

4,2512 gold badges12 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Using multiple columns from a nested SELECT in an INSERT query in sqlite3 [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related