Post reply

Delete Join

  • July 12, 2010 at 3:05 am

    #141309

    Hi I am tring to delete all data from a sub entity table and link table where a certain date range is <= to the one below.

    Here is the select query that gets the data i want to remove. I just need to turn it into a delete query. I have tried several example but can't get it working. Any ideas would be great. Thanks for looking.

    select top 10 notes, createtime from wce_history h join wce_linkto l on h.uniqueid = l.luniqueid where createtime <= '1980/01/01'

  • July 12, 2010 at 6:20 am

    #1191418

    I assume you want to delete the records from wce_history.

    delete h

    from wce_history h

    join wce_linkto l

    on h.uniqueid = l.luniqueid

    where l.createtime <= '1980/01/01'

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 12, 2010 at 7:47 am

    #1191468

    Thanks for the reply. I need to delete the associated record in the wce_linkto table too. For every row in the wce_history table there is a linked row in wce_linkto the link keys are wce_history.uniqueid = wce_linkto.luniqueid.

    Look forward to finding a solution for this. Thanks

  • July 12, 2010 at 8:38 am

    #1191504

    Is there a foreign key between the 2 tables? Is it possible for a record to be in the history table but not the linkto table?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 12, 2010 at 9:39 am

    #1191547

    Thanks for that, i'll have a read. In answer to your questions

    Is there a foreign key between the 2 tables? there is no foreign key

    Is it possible for a record to be in the history table but not the linkto table? This will never happen there will always be a record in the wce_linkto table if there is one added to wce_history

  • July 12, 2010 at 10:01 am

    #1191558

    If that's the case, then you can perform 2 separate deletes

    delete from wce_linkto

    where createtime <= '1980/01/01'

    delete h

    from wce_history h

    left outer join wce_linkto l

    on h.uniqueid = l.luniqueid

    where l.uniqueID is null

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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