Post reply

delete with cast option

  • January 22, 2011 at 10:03 am

    #100750

    delete from table1

    where cast(t1_field as char(10)) not in (select cast(t2_field as char(10)) from table2)

  • January 29, 2011 at 8:08 am

    #1280083

    Hello,

    The basic Jet SQL syntax for multi table delete with joins is:

    DELETE

    Table1 FROM Table1 WHERE EXISTS (SELECT t2 FROM table2 WHERE table1.t1 = Table2.t2) ;

    You can add the type conversions in by using the CStr function - Cast isn't supported in Jet SQL.

    DELETE

    Table1 FROM Table1 WHERE EXISTS (SELECT t2 FROM table2 WHERE CStr (table1.t1) = CStr (Table2.t2));

    John

  • January 29, 2011 at 8:45 am

    #1280085

    i didn't understand the whole thing. will somebody please help me in this thing?

    .........

  • January 31, 2011 at 7:08 am

    #1280405

    thanks...

  • February 1, 2011 at 12:59 am

    #1280782

    It seems to me that Roy's sql statement deletes all the records in t1 which aren't present in t2 while John's deletes all records in t1 which are also present in t2.

  • February 8, 2011 at 8:27 am

    #1283739

    This can be fixed by changing WHERE EXISTS to WHERE NOT EXISTS

  • February 9, 2011 at 12:48 pm

    #1284782

    i got this taken care of guys thanks for all the info.

    i don't have the exact syntax available right now but in my application it is all working fine.

    So....

    Thank you all very much.

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

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