Best Approach to remove data within a table
-
Hello
Lets say i have a table (table1) which has a number of account numbers for customers. Now then say i have multiple tables (tableA) and (tableB) and i want to remove all the data within (tableA) and (tableB) where there is a match in (table1). Usually i would do this using a simple join between the tables. Using TSQL is there a better way to do this? Can i maybe create a temp table, which holds the account number and then join that to (tableA) and (tableB) using a script and remove this way? Ideally i don't want a static table (table1) to exist within Account numbers in it. I want to manage this in some sort of script? It might be that a simple join is the best approach but i want to see what other options could be available.
Also - lets say that (tableA) and (tableB) use a different column name so the field i'm joining to in tableA is called AccountNumber but tableB it's called UnitNumber? Can this be wrote into the script?
Thank-you
-
Not exactly sure what the question is here. But you certainly don't need to create a temp table to hold one column of data from a permanent table to use as a join criteria. If you want to delete TableA where the Account is not in Table1 the only choice you have is tsql (a delete statement).
You can do that either with a left join or a not in.
--left joindelete tableafrom table1left join tablea a on table1.CustID = a.CustIDwhere a.CustID is null--not indelete tablea where CustID not in (Select CustID from table1)_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks for your reply...The response you put is the method i originally looked at, but i am just wondering "if" there might have been a more efficient way or a different way to doing it, without having a static table in that stores all the account numbers i wanted to delete. That's why i mentioned about maybe generating some sort of temp table that would then be removed once the procedure had completed ?
If not then thats fine. Thanks for getting back to me
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply