How do I insert into a table with a select statement?

Question

I have 2 tables with a join table. Here is my schema.

SOLDIERS SOLDIER_ID integer FIRST_NAME varchar(50) LAST_NAME varchar(50) RANKS RANK_ID integer NAME varchar(50) SOLDIER_RANKS SOLDIER_ID integer RANK_ID integer

I am trying to insert a row into the SOLDIER_RANKS table. I know the id of the SOLDIER and I know the name of the RANK. Here is what I have tried, but I can't figure out how to hard code the SOLDIER_ID.

 INSERT INTO SOLDIER RANKS(SOLDIER_ID, RANK_ID) // Here I need to specify that SOLDIER_ID = 1 SELECT RANK.RANK_ID WHERE RANK.NAME = 'COLONEL';

How do I make this work?

Bridge · Accepted Answer · 2014-03-18 09:58:39Z

No need to use a values clause:

INSERT INTO SOLDIER_RANKS(SOLDIER_ID, RANK_ID) SELECT 1, RANK.RANK_ID WHERE RANK.NAME = 'COLONEL';

bpgergo · Accepted Answer · 2014-03-18 09:55:46Z

INSERT INTO SOLDIER_RANKS(SOLDIER_ID, RANK_ID) values ( -- Here I need to specify that SOLDIER_ID = 1 1, (SELECT RANK.RANK_ID from <rank-table-name> RANK WHERE RANK.NAME = 'COLONEL') );

Vignesh Kumar A · Accepted Answer · 2014-03-18 10:02:08Z

Try these any of one method

INSERT INTO SOLDIER RANKS(SOLDIER_ID, RANK_ID) values ( 1, (SELECT RANK.RANK_ID FROM RANKS WHERE RANK.NAME = 'COLONEL') );

OR

 INSERT INTO SOLDIER RANKS(SOLDIER_ID, RANK_ID) SELECT 1, RANK.RANK_ID FROM RANKS WHERE RANK.NAME = 'COLONEL'

Collectives™ on Stack Overflow

How do I insert into a table with a select statement?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related