CODEWITHSUNDEEP
sqloracle-database
Peter Sun
Peter Sun

Reputation: 1813

ORACLE SQL: Inserting another ID from another row

I have write a series of SQL statements in an ORACLE database. In my database, I have 2 tables (book, publisher).

Below is the table structure

BOOK
----------
bk_id | title | pub_id

PUBLISHER
----------
pub_id | pub_name

If I insert into the publisher table first

INSERT INTO PUBLISHER (pub_name) VALUE ('ABC Publisher');

How do I retrieve the id of the publisher and enter it into the book table?

I usually do this with a stored procedure (SQL Server) or do it in the application.

How can I do it in ORACLE in SQL?

Upvotes: 0

Views: 75

Answers (3)

Jean-François Savard
Jean-François Savard

Reputation: 21004

I think the easiest way would consist in creating a Trigger that would insert in other table after table.

create or replace trigger tr_ai_publisher
after insert on publisher
for each row
begin
   --Here you can access the new publisher id using :new.pub_id
end;

That way, you would not have to handle yourself a call to the procedure.

However, if you really want to, you can also use a stored procedure in ORACLE, the general syntax is

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] 
IS    
   Declaration section 
BEGIN    
   Execution section 
EXCEPTION    
  Exception section 
END;

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

From PL/SQL, you might want to use the RETURNING INTO clause to get back the newly inserted id:

DECLARE
    my_id int;
BEGIN
    INSERT INTO PUBLISHER (pub_name) VALUE ('ABC Publisher')
      RETURNING id INTO my_id;
    ...
END;

where my_id is a PL/SQL variable declared accordingly to your column type.

Upvotes: 0

user4413591
user4413591

Reputation:

Just use a select statement:

INSERT INTO BOOK VALUES
(bk_id, title, (SELECT pub_id FROM PUBLISHER WHERE pub_name = publisher_name))
…
;

Replace bk_id, title, and publisher_name with the appropriate data you want.

Upvotes: 0

Related Questions