SQL insert use subquery result in another subquery

Question

I am trying to run the following query in postgresql to insert data to a table from the subquery results of two tables.

Here are my three sample tables -

CREATE TABLE a (
  id SERIAL PRIMARY KEY
  data int
);

CREATE TABLE b (
  id SERIAL PRIMARY KEY,
  aID INT 
  data INT
);

CREATE TABLE c (
  id SERIAL PRIMARY KEY,
  aID INT,
  bID INT,
  name VARCHAR
);

I'm using the following query to do an INSERT into the c table -

INSERT INTO c (
  aID,
  bID,
  name)
VALUES (
  (SELECT id FROM a WHERE data=$1),
  (SELECT id FROM b WHERE data=$2 AND aID=(SELECT id FROM a WHERE data=$1)),
  $3)
ON CONFLICT (bID)
DO NOTHING;

I was wondering whether it was possible to use the results of the first SELECT from the a table in the second subquery to reduce data access. I'm not sure this is the right way to go. Any help would be appreciated.

Removed the SQL Server and MySQL tags since you stated you are using PostgreSQL. — BJones
– BJones, Commented Jan 7, 2020 at 23:38
@BJones Thanks. I thought this was a generic sql question hence those tags. — Ice3man
– Ice3man, Commented Jan 7, 2020 at 23:38
The subquery in the second query is not the same as in the first, since it filters on a.data = $2. Is this a typo? — GMB
– GMB, Commented Jan 7, 2020 at 23:39
@GMB can you check my latest edit. Actually, that's what i want. So can you please modify your answer? — Ice3man
– Ice3man, Commented Jan 7, 2020 at 23:44

GMB · Accepted Answer · 2020-01-07 23:44:37Z

1

I was wondering whether it was possible to use the results of the first SELECT from the a table in the second subquery to reduce data access.

I suspect that you want a join instead:

INSERT INTO c (aID, bID, name)
SELECT a.id, b.id, $3
FROM a 
INNER JOIN b ON b.aid = a.id
WHERE a.data = $1 AND b.data = $2

This assumes that the "first select from the a table" and the "second subquery" are actually the same query (which is not exactly the case in your question where the first query filters on a.data = $1 while the subquery filters on a.data = $2). If the queries are different, then you are probably better off with two indenpendent queries).

edited Jan 7, 2020 at 23:44

answered Jan 7, 2020 at 23:40

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Ice3man Over a year ago

Let me try this! Thanks for the quick reply

Ice3man Over a year ago

Sorry, that second filter on a.data = $1 was a typo. Thanks for the response. I'll use this query and let you know how it goes!

Collectives™ on Stack Overflow

SQL insert use subquery result in another subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related