CODEWITHSUNDEEP
sqloracle-database
Barney_su
Barney_su

Reputation: 43

How do I use insert into from in sql when I need to insert from 2 tables joined together and another table

INSERT INTO reserve ( 
    branch_code, 
    bc_id, 
    reserve_date_time_placed, 
    bor_no )
SELECT b.branch_code, 
       c.bc_id, 
       TO_DATE('2018-09-20, 4:00 P.M.', 'YYYY-MM-DD, HH:MI P.M.'), 
       w.bor_no FROM borrower w
FROM borrower w, book_copy c
     JOIN branch b on c.branch_code = b.branch_code
WHERE b.branch_contact_no = '8888888883'
AND c.book_call_no = '005.74 C822D 2018'
AND w.fname = 'Ada';

I tried to insert into the reserve table the bor_no from borrower table individually, and other information from book_copy table joined with branch table.

So I think to better explain my question, I try to insert into reserve table from (book_copy joined with branch) and (borrower). But borrower and book_copy has no relationship.

borrower table is created like below.

    CREATE TABLE borrower (
    bor_no         NUMBER(6) NOT NULL,
    bor_fname      VARCHAR2(59) NOT NULL,
    bor_lname      VARCHAR2(50) NOT NULL,
    bor_street     VARCHAR2(80) NOT NULL,
    bor_suburb     VARCHAR2(50) NOT NULL,
    bor_postcode   CHAR(4) NOT NULL,
    branch_code    NUMBER(2) NOT NULL
);

Upvotes: 0

Views: 54

Answers (2)

Radu
Radu

Reputation: 1031

Take care that you have "from borower" twice in you example.

After that you could just use insert into ... select ...

INSERT INTO reserve ( 
branch_code, 
bc_id, 
reserve_date_time_placed, 
bor_no ) 
SELECT b.branch_code, 
       c.bc_id, 
       TO_DATE('2018-09-20, 4:00 P.M.', 'YYYY-MM-DD, HH:MI P.M.'), 
       w.bor_no 
FROM  book_copy c
     JOIN branch b on c.branch_code = b.branch_code
     JOIN bororwer w on c ..... = w. .... 
WHERE b.branch_contact_no = '8888888883'
AND c.book_call_no = '005.74 C822D 2018'
AND w.fname = 'Ada';

What is your error?

EDIT: You can do the insert only from book_Copy and branch like that:

INSERT INTO reserve ( 
branch_code, 
bc_id, 
reserve_date_time_placed, 
bor_no ) 
SELECT b.branch_code, 
       c.bc_id, 
       TO_DATE('2018-09-20, 4:00 P.M.', 'YYYY-MM-DD, HH:MI P.M.'), 
       w.bor_no 
FROM  book_copy c
     JOIN branch b on c.branch_code = b.branch_code
      WHERE b.branch_contact_no = '8888888883'
AND c.book_call_no = '005.74 C822D 2018'

After that get borrower_id into a variable 

select @borower_id from borrower where fname = 'Ada'

and then update reserve:

update reserve set borrower_id = @borrower_id 
where book_call_no = .... and branch_contact = .. ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

One error is your multiple FROM clauses. Try writing the query like this:

INSERT INTO reserve (branch_code, bc_id, reserve_date_time_placed, bor_no )
    SELECT b.branch_code, bc.bc_id, 
           TO_DATE('2018-09-20, 4:00 P.M.', 'YYYY-MM-DD, HH:MI P.M.'), 
           w.bor_no 
    FROM book_copy bc JOIN
         branch b 
         ON bc.branch_code = b.branch_code JOIN
         borrower w
         ON w.fname = 'Ada'
    WHERE b.branch_contact_no = '8888888883' AND
          bc.book_call_no = '005.74 C822D 2018';

Note that this uses proper JOIN syntax for all JOINs. There is no comma in the FROM clause.

Upvotes: 1

Related Questions