Home » SQL & PL/SQL » SQL & PL/SQL » delete record from table
delete record from table [message #603184] Thu, 12 December 2013 01:56 Go to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Hello friends,


I need your help that how to delete record from table A(stock_in) where value is not match from table B(stock_in_detail).

Kindly guide me how to make query of delete
I have made query of select . but no idea how to delete
Select a.stock_id,d.product_id from stock_in a, stock_in_
where a.stock_id<>d.stock_id


Here is tables detail

SQL> desc stock_in
Name Null? Type
------------------------------- -------- ----
STOCK_ID NOT NULL NUMBER(4)
PRINCIPLE_ID NOT NULL NUMBER(4)
WDATE DATE
PAID VARCHAR2(10)
INV NUMBER(20)
TNO VARCHAR2(50)


STOCK_ID PRINCIPLE_ID WDATE PAID INV TNO
--------- ------------ --------- ---------- --------- ----
1 1449 01-JAN-02 Paid 1
2 402 01-JAN-02 Paid
39 1491 23-SEP-13 Paid 13
4 1504 27-SEP-13 Paid 1
5 101 24-SEP-13 Paid 2
6 601 04-OCT-13 Paid 3
7 1458 04-OCT-13 Paid 3
8 1439 04-OCT-13 Paid 3
9 1002 04-OCT-13 Paid 3
10 401 03-OCT-13 Paid 4
SQL> desc stock_in_detail
Name Null? Type
------------------------------- -------- ----
STOCK_ID NOT NULL NUMBER(4)
PRODUCT_ID NOT NULL NUMBER(6)
QTY NUMBER(Cool
BATCH_NO NOT NULL VARCHAR2(15)
WDATE NOT NULL DATE

SQL> select * from stock_in_detail
2 /

STOCK_ID PRODUCT_ID QTY BATCH_NO WDATE
--------- ---------- --------- --------------- ---------
4 17026 6 .. 28-FEB-17
4 17027 3 .. 28-FEB-17
4 17028 13 .. 28-FEB-17
4 17029 13 .. 28-FEB-17
4 17030 96 .. 28-FEB-17
5 104 10 .. 28-FEB-17
5 103 100 .. 28-FEB-17
5 102 100 .. 28-FEB-17
5 106 100 .. 28-FEB-17


SQL> select a.stock_id,d.product_id from stock_in a, stock_in_
2 where a.stock_id<>d.stock_id
3 /

STOCK_ID PRODUCT_ID
--------- ----------
1 17026
2 17026
39 17026
5 17026
6 17026
7 17026
8 17026
9 17026
10 17026
11 17026
12 17026
40 17026
41 17026
16 17026
17 17026

Report message to a moderator

Re: delete record from table [message #603188 is a reply to message #603184] Thu, 12 December 2013 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
 
delete from A
where id not in (select id from B)

Report message to a moderator

Re: delete record from table [message #603212 is a reply to message #603188] Thu, 12 December 2013 03:17 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Thanks

Report message to a moderator

Re: delete record from table [message #603231 is a reply to message #603212] Thu, 12 December 2013 07:32 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
just to add when you have huge amount of data then use exists.

e.g.
DELETE FROM A
      WHERE NOT EXISTS (SELECT 1 
                          FROM B 
                         WHERE B.id = A.id)


Cause: EXISTS/NOT EXISTS are quicker than IN/NOT IN.

thanks

Report message to a moderator

Re: delete record from table [message #603233 is a reply to message #603231] Thu, 12 December 2013 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is wrong, it depends on many things and Oracle optimizer knows which one it has to use even if you give the other ones (assuming the statistics are up to date).

In addition, for the record, NOT IN and NOT EXISTS are not equivalent unless the column "id" is not null (which is the case here).

Report message to a moderator

Re: delete record from table [message #603790 is a reply to message #603233] Thu, 19 December 2013 07:35 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi friend,

Please find the query as you expected :


delete from stock_in
where  stock_id 
in(
    select   a.stock_id as stock_id
    from     stock_in a, 
             stock_in_detail b
    where    a.stock_id <>  d.stock_id 
   );

Report message to a moderator

Re: delete record from table [message #603792 is a reply to message #603790] Thu, 19 December 2013 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
@msol25 - I appreciate the fact that you are trying to help, but you really need to stop this mission to post a full solution on every recent question.
Several of your answers so far today have either been:
a) repeats of solutions already posted by others.
b) less efficient variations of solutions already posted by others.
c) solutions that don't actually work at all.

The above falls into category c.
It won't compile because you have a typo and if you fix that and run it it'll delete every row in the table.
LF has already given the correct solution.

I strongly suggest you spend a bit less time posting and a bit more time checking your solution works and hasn't already been suggested by someone else.

Report message to a moderator

Re: delete record from table [message #603863 is a reply to message #603792] Fri, 20 December 2013 01:26 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Why you are telling like this...?

I m giving my solution,it doesn't matter somebody given or not....if solution is correct then there should not be any issue

[Updated on: Fri, 20 December 2013 01:27]

Report message to a moderator

Re: delete record from table [message #603865 is a reply to message #603863] Fri, 20 December 2013 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I disagree,
Why do you repeat correct solution? It is useless, helpless, everyone can do that, it just add noise to topics.
Why don't you test your "solution"? Why do you post wrong "solution"? It is...
Here are the issues, don't you think?

Report message to a moderator

Re: delete record from table [message #603873 is a reply to message #603863] Fri, 20 December 2013 02:35 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Fri, 20 December 2013 07:26
Why you are telling like this...?

I m giving my solution,it doesn't matter somebody given or not....if solution is correct then there should not be any issue


I've already pointed out that it isn't correct. It's very badly wrong.
Stop rushing to answer and start checking what you are saying.

Report message to a moderator

Re: delete record from table [message #603926 is a reply to message #603863] Fri, 20 December 2013 06:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
msol25 wrote on Fri, 20 December 2013 02:26
Why you are telling like this...?

I m giving my solution,it doesn't matter somebody given or not....if solution is correct then there should not be any issue


As others already pointed out you are rushing into solutions, and not just for this post, and in many cases your solutions are inefficient at best. In this particular case your solution is dead wrong. Assume we have stock_id 1, 2 & 3. There is stock_detail for stock_id 1 and 2 but no stock_detail for stock_id 3. Now think what

    select   a.stock_id as stock_id
    from     stock_in a, 
             stock_in_detail b
    where    a.stock_id <>  d.stock_id


will return and therefore what your suggested solution will delete.

SY.

Report message to a moderator

Re: delete record from table [message #603928 is a reply to message #603926] Fri, 20 December 2013 07:00 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, a simpler test would be: think what will your solution delete when every stock_id has stock detail?

SY.

Report message to a moderator

Previous Topic: MAX value of column
Next Topic: Partitioning of tables
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 20:03:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.