Post reply

Delete records wich exist in other table

  • July 18, 2006 at 4:41 pm

    #114297

    Hi, I have a table with monthly generation. The new data it's added to an historic table.

    The idea it's to check and eliminate any duplicate data with the historic table. For this purpose it's necesary to compare two identical fields in each table.

    I'm using this script ( very fast ) to list the data not duplicated but I need to build the reverse thing and I'm not findeing the way...

    SELECT hon.v, hon.t

    FROM CxCHon AS hon LEFT JOIN Historic AS his ON (hon.Terc=His.Terc) AND (hon.Val=his.Val)

    WHERE his.Val is null;

    Thanks in advance...

    Luis Ernesto

  • July 18, 2006 at 4:47 pm

    #649987

    -- Find duplicate data in history table

    SELECT    Terc,

              Val,

              COUNT(*)

    FROM      Historic

    GROUP BY  Terc,

              Val

    HAVING    COUNT(*) > 1

    -- Find duplicate values between the two tables

    SELECT      hon.val,

                hon.terc,

                COUNT(*)

    FROM        CxCHon AS hon

    INNER JOIN  Historic AS his ON hon.Terc = His.Terc AND hon.Val = his.Val

    GROUP BY    hon.val,

                hon.terc

    HAVING      COUNT(*) > 1

    N 56°04'39.16"
    E 12°55'05.25"

  • July 18, 2006 at 6:03 pm

    #649992

    Thanks Peter,

    I need to delete the data into de fist table, that means any data from hon wich exist in table historic, must be deleted from hon table, using the two fields to compare.

  • July 18, 2006 at 11:27 pm

    #650011

    --SELECT * FROM CxCHon

    DELETE FROM CxCHon

    WHERE EXISTS (SELECT * FROM Historic WHERE Terc = CxCHon.Terc AND Val = CxCHon.Val)

    Andy

  • July 18, 2006 at 11:45 pm

    #650013

    DELETE C

    FROM CxCHon C

    INNER JOIN Historic H ON H.Terc = C.Terc AND H.Val = C.Val

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply