|
> To: pgsql-general@xxxxxxxxxxxxxx
> Date: Tue, 26 Nov 2013 08:15:45 +0100 > > Nelson Green, 25.11.2013 23:01: > > Hello, > > When inserting a record into the jobs table that references projects by name, do I have to query the projects table twice, > > once to get the funding source number, and once to get the project sequence number, even though both results will > > return the same row? Or put another way, is there a way to insert a row into the jobs table without having to > > perform two sub-queries for the same row, thus avoiding this: > > > > INSERT INTO jobs > > VALUES ((SELECT fundsrc_number FROM projects > > WHERE project_name = 'proj1-1'), > > (SELECT project_seq FROM projects > > WHERE project_name = 'proj1-1'), > > 1, 'job1-1.1', 'first project 1-1 job'); > > > > Use an INSERT based on a SELECT, not based on VALUES: > > INSERT INTO projects (fundsrc_number, project_seq, project_name, project_desc) > SELECT fundsrc_number, 1, 'proj1-1', 'first source01 project' > FROM fundsrc > WHERE fundsrc_name IN ('source01', 'source02'); > > INSERT INTO jobs (fundsrc_number, project_seq, job_seq, job_name, job_desc) > SELECT fundsrc_number, project_seq, 1, 'job1-1.1', 'first project 1-1 job' > FROM projects > WHERE project_name = 'proj1-1'; This works perfectly. I could swear I tried something similar and was unsuccessful, but I can't find any indication that I did in my history or my notes. Regardless, if you heard a loud smacking noise a few seconds ago that was my palm hitting my forehead. I appreciate you taking the time to state the obvious to the obviously blind. > Note that it's good coding style to always specify the columns in an INSERT statement. > It makes your statements more robust against changes. I do. I just saved a few keystrokes and a bit of reading for this simple example. I know to specify and qualify at all times. Thanks for both tips! |
