none
Delete and Update SQL statement

    Question

  • I've been asked by the Accounting team to purge (e.g. delete) all vendor records that are not currently supplying any products you carry.

     There should be 3 of them that are removed from Vendors (but don't use SQL that hard-codes the Vendors by id- should be generic and work with any data in the table)

     

    can someone help me write the SQL statement to accomplish this?

    Friday, May 17, 2019 2:53 AM

Answers

  • This must need a reference, for example the query should be referring to Supply Table. Consider something similar to below

    DELETE FROM Account_table
    WHERE VendorID in
    (Select VendorID from Supply where last_shipment data < Getdate()-365)

    The above will delete the vendor records from account_table who hasnt shipped anything in last one year. If you are planning to clean up the supplier from entire system, you need to review the Vendor parent table and find child records and then delete one by one and finally from the parent table. 

    ------------------------------------------------------------
    Please remember to mark the replies as answers if they help 

    Friday, May 17, 2019 4:27 AM

All replies

  • This must need a reference, for example the query should be referring to Supply Table. Consider something similar to below

    DELETE FROM Account_table
    WHERE VendorID in
    (Select VendorID from Supply where last_shipment data < Getdate()-365)

    The above will delete the vendor records from account_table who hasnt shipped anything in last one year. If you are planning to clean up the supplier from entire system, you need to review the Vendor parent table and find child records and then delete one by one and finally from the parent table. 

    ------------------------------------------------------------
    Please remember to mark the replies as answers if they help 

    Friday, May 17, 2019 4:27 AM
  • Does this help? Please let me know if any questions

    ---------------------------------------------------------------
    Please remember to mark the replies as answers if they help 

    Monday, May 20, 2019 1:31 AM
  • Hi B-Ginner,

    Please refer to DELETE (Transact-SQL) to write the SQL statement.

    Hope this could help you.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 20, 2019 11:48 AM