0

i have a query like this:

insert into book ('book_id', 'category_id', 'book_name', 'buy_price', 'sell_price') values ('B-001, 'BOM-001', 'Tarzan', 200, 300);

is it possible to get the category_id from another table which is category table using select and condition where category_name = 'adventure' so i get the BOM-001? can anyone give me a sample of that query ?

thank you

yazid nasution
  • 11
  • 3
  • 1
    Please show us sample data and expected results. – GMB Nov 09 '19 at 15:52
  • 1
    keeping aside **category_id** column, how do you perform insert operation? Do you have an attempt even it seems not logical, or not syntatically correct ..? – Barbaros Özhan Nov 09 '19 at 16:02
  • Single quotes (`'`) are string delimiters. If you need name delimiters (you don't, as your names neither contain special characters, nor do they equal keywords), use double quotes (`"`). – Thorsten Kettner Nov 09 '19 at 16:25

2 Answers2

0

This would look like:

insert into book (book_id, category_id, book_name, buy_price, sell_price)
    select ?, c.category_id, ?, ?, ?
    from category c
    where c.category_name = ?;

The ? are placeholders for your constant values. Note that there are no single quotes around the column names for the insert.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

There are two ways to achieve what you want.

First: a single value can always be replaced by a query that returns a single value:

insert into book (book_id, category_id, book_name, buy_price, sell_price)
values ('B-001', 
        (select category_id from category where category_name = 'adventure'),
        'Tarzan',
        200,
        300
       );

Second: You can insert rows you select from somewhere:

insert into book (book_id, category_id, book_name, buy_price, sell_price)
select 'B-001', category_id, 'Tarzan', 200, 300
from category where category_name = 'adventure';
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • i've tried the second option you gave me and it worked like a charm, thank you very much. i wonder is there a way(query)/short way if im gonna inserting multiple rows/records ? – yazid nasution Nov 09 '19 at 16:37
  • The second statement is capable of inserting multiple rows at once, e.g. by recplacing `category_name = 'adventure'` with `category_name in ('adventure', 'fun')`. – Thorsten Kettner Nov 09 '19 at 16:48