Home » SQL & PL/SQL » SQL & PL/SQL » logic to check record exist in tabl (oracle 10g)
logic to check record exist in tabl [message #577202] Wed, 13 February 2013 06:31 Go to next message
guddu_12
Messages: 166
Registered: April 2012
Location: UK
Senior Member
Hi

Usually we check 1 row of data in table and compare if they exist, i want to compare different set of row all together to comapre and take the decisions

eg.

emp table_source
d_key emp_no invol
1 103499 1
1 99262 2
2 103499 1
3 103499 1
3 99262 2


target table

emp_no invol
103499 1
99262 2
103499 1

in the above example i have to check for d_key for set of row exist in target or not, so by above example d_key 1 and 3 has save value in 2 different rows so i want to check if the combination of rows
103499 1
99262 2
as a set appear in source then do nothing else insert so first time d_key 1 both records will insert but for d_key has same set in 2 rows will not be inseted. the number of row can be more than 2 also . so for the given key if we have 3 rows in source then i have to compare all the 3 same set of row in target exist of . in the target i don't have any extra column.
Any help will be greatly appricieated


Re: logic to check record exist in tabl [message #577204 is a reply to message #577202] Wed, 13 February 2013 06:44 Go to previous messageGo to next message
javed.khan
Messages: 291
Registered: November 2006
Location: Banglore
Senior Member

Use Merge.
Re: logic to check record exist in tabl [message #577206 is a reply to message #577204] Wed, 13 February 2013 06:52 Go to previous messageGo to next message
guddu_12
Messages: 166
Registered: April 2012
Location: UK
Senior Member
but how to check together 3 rows or may be more in target as a whole
Re: logic to check record exist in tabl [message #577210 is a reply to message #577206] Wed, 13 February 2013 08:06 Go to previous message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As source table to merge do not use the table itself but a select that keeps only the first occurrence of duplicates (as far as I understand what you said):
select emp_no, invol
from (select emp_no, invol, row_number() over (partition by emp_no order by d_key) rn
      from source_table)
where rn = 1

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Previous Topic: Single-row subquery Issue
Next Topic: Need help in reducing the steps in query which is attached
Goto Forum:
  


Current Time: Mon Apr 21 04:45:48 CDT 2014

Total time taken to generate the page: 0.09341 seconds