unexpected output from sql query -oracle

Question

i have 4 tables products , orders, order_hist and order_prod_ref. data setup is provided below:

DROP TABLE products;
DROP TABLE orders;
DROP TABLE order_hist;
DROP TABLE ord_prd_ref;
create table products (product_id varchar2(20),FLG CHAR(1));
create table orders (order_id varchar2(20));
create table order_hist (order_id varchar2(20));
create table ord_prd_ref (product_id varchar2(20),order_id varchar2(20),prd_name varchar2(10));

INSERT INTO PRODUCTS values ('1000365007482','Y');
INSERT INTO PRODUCTS values ('1000359547456','N');
INSERT INTO PRODUCTS values ('1000359524206','N');
INSERT INTO PRODUCTS values ('1000082514435','N');
INSERT INTO PRODUCTS values ('1000088066693','N');
commit;

INSERT INTO ORDERS VALUES ('5000099148559');
INSERT INTO ORDERS VALUES ('5000099236099');
INSERT INTO ORDERS VALUES ('5000099705242');
INSERT INTO ORDERS VALUES ('5000002349523');
INSERT INTO ORDERS VALUES ('5000002349523');
INSERT INTO ORDERS VALUES ('5000099148559');
COMMIT;

INSERT INTO ord_prd_ref VALUES ('1000365007482','5000099148559','A');
INSERT INTO ord_prd_ref VALUES ('1000365007482','5000099148559','B');
INSERT INTO ord_prd_ref VALUES ('1000365007482','5000099148559','C');
INSERT INTO ord_prd_ref VALUES ('1000365007482','5000099148559','D');
INSERT INTO ord_prd_ref VALUES ('1000359547456','5000099236099','E');
INSERT INTO ord_prd_ref VALUES ('1000359547456','5000099236099','F');
INSERT INTO ord_prd_ref VALUES ('1000359547456','5000099236099','G');
INSERT INTO ord_prd_ref VALUES ('1000082514435','5000099705242','H');
INSERT INTO ord_prd_ref VALUES ('1000088066693','5000099236099','I');
INSERT INTO ord_prd_ref VALUES ('1000082514435','5000099705242','J');

    INSERT INTO ord_prd_ref VALUES ('1000082514435','5000099705242','K');
    COMMIT;

i want to insert data into order_hist from orders when products.flg='Y' and also delete that record from order table .

i tried this but its not giving me expected results as there is only one product_id with flg='Y' i.e 1000365007482

INSERT INTO order_hist
select * FROM orders a
WHERE
    EXISTS (
        SELECT
            NULL
        FROM
           products,
            ord_prd_ref,
            orders
        WHERE
            products.product_id = ord_prd_ref.product_id
            AND orders.order_id = ord_prd_ref.order_id
            AND FLG = 'Y'
           AND products.FLG = 'Y'
           );

expected result should be the corresponding order_id should be inserted into order_hist table i.e 5000099148559.

 select order_id from ord_prd_ref where product_id = '1000365007482';

any help is much appreciated.

Thanks in advance.

Ted at ORCL.Pro · Accepted Answer · 2019-02-18 14:54:52Z

1

Try this:

INSERT INTO order_hist
SELECT distinct orders.order_id 
        FROM
           products,
            ord_prd_ref,
            orders
        WHERE
            products.product_id = ord_prd_ref.product_id
            AND orders.order_id = ord_prd_ref.order_id
           AND products.FLG = 'Y'

DELETE from orders where order_id = (
SELECT distinct orders.order_id 
            FROM
               products,
                ord_prd_ref,
                orders
            WHERE
                products.product_id = ord_prd_ref.product_id
                AND orders.order_id = ord_prd_ref.order_id
               AND products.FLG = 'Y')

DELETE from ord_prd_ref where order_id = (
SELECT distinct orders.order_id 
                    FROM
                       products,
                        ord_prd_ref,
                        orders
                    WHERE
                        products.product_id = ord_prd_ref.product_id
                        AND orders.order_id = ord_prd_ref.order_id
                       AND products.FLG = 'Y')

edited Feb 18, 2019 at 14:54

answered Feb 18, 2019 at 14:46

Ted at ORCL.Pro

1,6121 gold badge11 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kashi Over a year ago

thanks @Ted it worked for insert but what about delete ? it should delete from orders table as well

OTTA Over a year ago

Think it through. If you can insert into order_hist by selecting the relevant order_id values from orders etc then you can delete from orders by selecting the relevant order_id values from order_hist. It's a seperate query.

Ted at ORCL.Pro Over a year ago

@kashi I edited my answer and provided you with the 2 delete statements you need for tables orders and ord_prd_ref. if it all satisfies your question please don't forget to marks the answer as correct.

forpas · Accepted Answer · 2019-02-18 14:58:07Z

This:

select distinct o.order_id
from products p inner join ord_prd_ref opr
on opr.product_id = p.product_id
inner join orders o
on o.order_id = opr.order_id
where p.flg = 'Y'

returns all the order ids that you need.
So insert them to order_hist:

insert into order_hist
select * from orders
where order_id in (
    select distinct o.order_id
    from products p inner join ord_prd_ref opr
    on opr.product_id = p.product_id
    inner join orders o
    on o.order_id = opr.order_id
    where p.flg = 'Y'
)

and delete them from orders:

delete from orders
where order_id in (
    select distinct o.order_id
    from products p inner join ord_prd_ref opr
    on opr.product_id = p.product_id
    inner join orders o
    on o.order_id = opr.order_id
    where p.flg = 'Y'
)

Collectives™ on Stack Overflow

unexpected output from sql query -oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related