ORACLE QUERY [message #313015] Thu, 10 April 2008 06:33 Go to next message
Messages: 1
Registered: April 2008
Junior Member
From the following data
L000304T 117930 M
L001538N 126199 R
L001538N 126191 M
L006339H 150427 N
L008519X 139085 R
L008519X 139075 M
L008579J 402452 R
L008579J 144668 M
L008579J 402429 R
L043884W 447519 R
L043885H 303383 C
L043886X 303383 R

The valid records are those with R
We want to delete only those records with the IDs are in valid record but REF_NUM is not corresponding to that valid id.
e.g in the above case
L001538N 126199 R --- is valid hence
L001538N 126191 M -- is invalid, to be deleted
similarly the other invalid records to be deleted are
L008519X 139075 M
L008579J 144668 M

We tried the query below, it is doesnt work.
select *
from FLAG_LKP del
Where del.REF_NUM not in
(select sel.REF_NUM -- selects all records with FLAG as R
from FLAG_LKP sel
where sel. FLAG = 'R'
and sel.ID = del.ID)

The catch is that in the outer query you should not use del.FLAG<> R.
I have put FLAG = R so as to present the problem simply. Actually the selection criteria (which will replace FLAG = R) results from a multiple table join.
To put FLAG <> R in outer query would be to replicate the multi table join which I don't want
Re: ORACLE QUERY [message #313017 is a reply to message #313015] Thu, 10 April 2008 06:40 Go to previous message
Michel Cadot
Messages: 65090
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You break so many rules of this forum that I don't where to start.

Maybe read OraFAQ Forum Guide
Read what is said about upper case.
Read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Also this is NOT an expert question.
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here

Read rules, follow them, then we will be glad to help you.


[Updated on: Thu, 10 April 2008 06:41]

Report message to a moderator

Previous Topic: Delete from 2 tables at the same time
Next Topic: date format not working
Goto Forum:

Current Time: Thu Jul 27 09:59:09 CDT 2017

Total time taken to generate the page: 0.10835 seconds