Showing posts from April, 2010

Delete from a table which requires lookup in another table

Problem: To delete rows from TableA based on filter condition which requires a lookup on another table TableB.
Solution 1)
DELETE FROM TableA FROM TableA as A INNER JOIN TableB as B on A.ID = b.ID WHERE B.COL2 = ''someval' and B.COL3 = 'someval'
Solution 2)
DELETE FROM TableA WHERE ID IN (SELECT ID FROM TableB where COL2 = ''someval' and COL3 = 'someval')

Get distinct rows from DataTable

Found a method in data table object to get distinct rows:
DataTable dtTest = dtProduct.DefaultView.ToTable(true, new string[] { "PRODUCT", "NATURE", "TYPE" });
The first parameter has to be 'true' to get distinct rows, the second is the list of columns to return.