Discussions
Categories
- 196K All Categories
- 2.1K Data
- 209 Big Data Appliance
- 1.9K Data Science
- 447.4K Databases
- 220.8K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 516 MySQL Community Space
- 463 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 469 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.9K SQL & PL/SQL
- 20.9K SQL Developer
- 292.6K Development
- 7 Developer Projects
- 124 Programming Languages
- 289.4K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 23 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.8K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 15 Java Essentials
- 143 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 240 LiveLabs
- 35 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Delete record
Hi,
Created three tables and group by 3 tables column name. want to delete duplicate record without first table(test).
Delete the duplicate record in test1 and test2 except test. kindly help me.
SELECT a as Name,b as M_Name, c as L_Name, count(*)
FROM (
SELECT first_name as a, middle_name as b, last_name as c FROM test
UNION ALL
SELECT first_name as a, middle_name as b, last_name as c FROM test1
UNION ALL
SELECT first_name as a, middle_name as b, last_name as c FROM test2
) as count
GROUP BY a,b,c
HAVING count(*) > 1
Answers
-
Can each table have duplicate record in it?
Lets say you have a record like
first_name - karthick
middle_name - x
last_name - arp
test has 3 records with above data
test1 has 2 records with above data
test2 have 2 records with above data
Now what are the records would you like to delete.
-
Hi,
after group the date delete test1 and test2.
-
I guess you are looking for something like:
{code}
delete from test1 a
where exists (select 'x' from test b
where a.first_name = b.first_name
and a.middle_name = b.middle_name
and a.last_name = b.last_name);
delete from test3 a
where exists (select 'x' from test b
where a.first_name = b.first_name
and a.middle_name = b.middle_name
and a.last_name = b.last_name)
or exists (select 'x' from test1 b
where a.first_name = b.first_name
and a.middle_name = b.middle_name
and a.last_name = b.last_name);{code}
Here you delete any rows in TEST1 that is in TEST. And you delete any rows in TEST2 that is in either TEST1 or TEST.
-
delete test1 t1
where exists (select null
from test t
where (t.first_name = t1.first_name
or (t.first_name is null and t1.first_name is null)
)
and (t.middle_name = t1.middle_name
or (t.middle_name is null and t1.middle_name is null)
)
and (t.last_name = t1.last_name
or (t.last_name is null and t1.last_name is null)
)
);
delete test1 t2
where exists (select null
from test t
where (t.first_name = t2.first_name
or (t.first_name is null and t2.first_name is null)
)
and (t.middle_name = t2.middle_name
or (t.middle_name is null and t2.middle_name is null)
)
and (t.last_name = t2.last_name
or (t.last_name is null and t2.last_name is null)
)
);
To remove rows from Test1/2 where rows exist in test. Any rows duplicated in test only will remain.
-
Hi becol,
Thanks for you reply.
you are comparing each and every table and then delete.
is there any way using single query. want to use single delete statement only.
-
Sorry. You can't delete from more than one table with a single delete statement.