Forum Stats

  • 3,758,427 Users
  • 2,251,387 Discussions
  • 7,870,189 Comments

Discussions

Delete record

User_JIAY3
User_JIAY3 Member Posts: 112 Blue Ribbon
edited Jul 29, 2013 7:08AM in SQL & PL/SQL

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

Tagged:
User_JIAY3Rajat

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    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.

  • User_JIAY3
    User_JIAY3 Member Posts: 112 Blue Ribbon

    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.

    User_JIAY3
  • bencol
    bencol Member Posts: 894 Bronze Badge
    edited Jul 29, 2013 6:33AM

    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.

    User_JIAY3
  • User_JIAY3
    User_JIAY3 Member Posts: 112 Blue Ribbon

    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.

  • kendenny
    kendenny Member Posts: 1,269

    Sorry. You can't delete from more than one table with a single delete statement.

    User_JIAY3Rajat
This discussion has been closed.