delete items not in file

• Colin Crawford

  Newbie

  Points: 1

  More actions

  July 12, 2005 at 12:22 am

  #164313

  Hi,

  I am a newbie when it comes ot sql. Just need a simple script to delete items from a database from a particular supplier that the product id's are not found in an uploaded csv file to my server.

  Any help appreciated.

  Thanks,

  Col

• jordanac

  Hall of Fame

  Points: 3653

  More actions

  July 12, 2005 at 4:17 am

  #573291

  HI, TRY THIS.....

  Before you run it maybe you can change the delete to select * to test.

  DELETE dbtable

  FROM         csvtable RIGHT OUTER JOIN

                        dbtable ON csvtable.id = dbtable.id AND csvtable.supplier = dbtable.supplier

  WHERE     (csvtable.id IS NULL)

  (did you import the csv file into the database?)

   

  
  Andy.

• jordanac

  Hall of Fame

  Points: 3653

  More actions

  July 12, 2005 at 4:51 am

  #573292

  Hi,

  If the csv file is not to large and you have not imported it you could use the following....

  DELETE dbtable

  FROM dbtable AS d LEFT OUTER JOIN

  OpenRowset(

  'MSDASQL',

  'Driver={Microsoft Text Driver (*.txt; *.csv)};

  DefaultDir=C:\;','select ID,Supplier from csvTable.CSV')

  AS C ON D.[ID] =C.ID AND

  D.Supplier = C.Supplier

  WHERE (c.[ID] IS NULL)

  
  Andy.