Compare Column/Field Oracle PL/SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Compare Column/Field Oracle PL/SQL

Postby JDSal » Fri Jul 26, 2013 3:11 pm

Is there a way to compare two tables on say id_number(s), just this one column/field? What I am looking to do is find all the id-numbers that match all the id-numbers on another table and delete matched id’s from one of these tables.
Thanks
JDSal
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: Compare Column/Field Oracle PL/SQL

Postby Tim... » Sat Jul 27, 2013 8:03 am

Hi.

Tables:

Code: Select all
CREATE TABLE t1 AS
SELECT level AS id
FROM   dual
CONNECT BY level <= 10;

CREATE TABLE t2 AS
SELECT level AS id
FROM   dual
CONNECT BY level <= 5;


Rows that are present in both tables.

Code: Select all
SELECT id
FROM   t1
INTERSECT
SELECT id
FROM t2;

        ID
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL>


One option:

Code: Select all
DELETE FROM t1
WHERE id IN (SELECT id
             FROM   t1
             INTERSECT
             SELECT id
             FROM t2);


Another option:

Code: Select all
DELETE FROM t1
WHERE EXISTS (SELECT 1
              FROM   t2
              WHERE  t1.id = t2.id);


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 6 guests

cron