Need to delete the row from the table which is duplicate on entire row

General
Enthusiast

Need to delete the row from the table which is duplicate on entire row

1) should not have any changes to the target table structure ( i mean we should n't alter the table)

Table T1 contains

Col1, Col2

1,A

1,A

2,B

2,B

2,B

3,C

4,D

4,E

4,F

delete the entire duplicate row from the table T1 and result should be in T1 is

1,A

2,B

3,C

4,D

4,E

4,F

the query need to work in Teradata server

Tags (1)
1 REPLY
Junior Supporter

Re: Need to delete the row from the table which is duplicate on entire row

This has been asked and answered over and over...

Your mileage may vary, but in the end you can apply some variations of:

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT * FROM CARLOS.PRUEBA03 ORDER BY 1;
BTEQ -- Enter your SQL request or BTEQ command:

SELECT * FROM CARLOS.PRUEBA03 ORDER BY 1;


 *** Query completed. 9 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

          ID_N  C_TXT

--------------  -------------------------

            1.  A

            1.  A

            2.  B

            2.  B

            2.  B

            3.  C

            4.  F

            4.  E

            4.  D

 BTEQ -- Enter your SQL request or BTEQ command:

INSERT INTO CARLOS.PRUEBA03

SELECT -1*ID_N, C_TXT

  FROM CARLOS.PRUEBA03

 GROUP BY ID_N, C_TXT HAVING COUNT(1) > 1

;

 *** Insert completed. 2 rows added.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

DELETE

  FROM CARLOS.PRUEBA03 a

 WHERE EXISTS (SELECT NULL

                 FROM CARLOS.PRUEBA03 b

                WHERE B.ID_N < 0

                  AND b.ID_N = -1 * a.ID_N

                  AND b.C_TXT = a.C_TXT )

;

 *** Delete completed. 5 rows removed.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

UPDATE CARLOS.PRUEBA03

   SET ID_N = -1*ID_N

 WHERE ID_N < 0

;

 *** Update completed. 2 rows changed.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT * FROM CARLOS.PRUEBA03 ORDER BY 1, 2;

 *** Query completed. 6 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

 *** Query completed. 6 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

          ID_N  C_TXT

--------------  -------------------------

            1.  A

            2.  B

            3.  C

            4.  D

            4.  E

            4.  F

HTH.

Cheers.

Carlos.