The query to identify duplicate records in Oracle in a single table is fairly straightforward. Things get tricky in a two-table (master-detail) scenario, in which the master table holds the header information (id, name, etc) and the detail table has sets of values associated with the master records.
What if we need to find those master records that have identical sets of values in the detail table? Can a single SQL list master records with identical detail records?
{ 0 comments }
Oracle’s MERGE statement is tailor-made for situations when you want to do an "upsert" i.e. update existing rows in a table or insert new rows depending on a match condition. This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query). In place of 3 separate unwieldy INSERT, UPDATE and DELETE statements with conditional sub-queries, the all-in-one MERGE does the job in one shot.
{ 18 comments }
A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.
For example, a query to list employees whose salary is more than their department’s average:
{ 3 comments }
A subquery is – to put it simply – a query within a query.
A subquery may be needed when it takes more than a single step to reach the answer.
Suppose we need to find all employees who work in the same department as KING. We need to:
{ 0 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
