asked
Delete a row when it is not existing in other table
Hello Everyone, SQL SERVER: I have tables A and B, if any changes happen in B, should be updated in A. But I can't find a solution for deleting records if it is not existing in B, as Table A gets data from a lot of tables but it should delete the data which is not existing in B. For example: A has 400 records B has 200 records C has 200 records. Here table A should compare only with table B but not with C. If I delete a record in B then the count of A should be 399. Using after Delete trigger is Restricted. Can anyone please help me :|
Could you send a sample of data with expected results, it will be easier for us
@jangi In your example, is there a way to identify where the records in table A came from (B or C). This is needed in order to filter the records in A to consider only the records which originally came from B. This could be something as simple as the IDs between B and C never clash, so that if table B has 200 records and table C has 200 records then table A has 400 records all with unique IDs, 200 from B and 200 from C. Also, please clarify the version of SQL Server you have. If it is SQL Server 2005 or any later version then you might want to consider using the output clause, no need to use any triggers, the deletes can be done in real time. Thank you.
@Oleg ID's are completely random in B and C. I got an Idea but I don't know how to implement it. I can create a column in both A and B tables with Btablename. So now in A table and B table under the new column, we can see B table name. Now, do we have any solution to compare between A and B and to delete only the records which are deleted in B? My task is to create a Stored Procedure for Update, Insert and Delete from B to A, I struck at delete.
ID's are completely random in B and C. I got an Idea but I don't know how to implement it. I can create a column in both A and B tables with Btablename. So now in A table and B table under the new column, we can see B table name. Now, do we have any solution to compare between A and B and to delete only the records which are deleted in B? My task is to create a Stored Procedure for Update, Insert and Delete from B to A, I struck at delete.
There are 2 ways to synchronize the tables (in real time and some time later). There are, therefore, 2 solutions in this answer. I will assume that there is a column (lets call it RecordID which uniquely identifies the record in every table, so that if table B has 200 records and table C has 200 records then table A has 400 records but all its respective RecordID values are unique and came from tables B and C. For real time solution, it is possible to utilize the [output clause][1], which is available in SQL Server 2005 or any later version. Here is the sample script which can be used in place of the existing script deleting some rows from table B, such as **delete dbo.B where { some condition goes here }** -- Method 1, shown for delete from B, deletes from C can be handled similarly -- Delete some rows from B, and also cause a delete of matching rows from A declare @t table (RecordID int not null) delete dbo.B output deleted.RecordID into @t (RecordID) where RecordID between 296 and 300; /* or whatever desired condition */ -- The RecordID values of the affected (deleted) rows from dbo.B are now -- available and can be used to delete the matching rows from dbo.A delete a from dbo.A a inner join @t t on a.RecordID = t.RecordID; go If the above script deleting the rows from A in real time is not acceptable (or it is already too late as some rows have been already deleted and there is a need to sync the changes now) then the script below should work and can be executed at any time. It assumes that there is no column which identifies the source of the record in A, the records can be matched by ID, but there is no separate column. This explains the union all of B or C: -- Method 2, the deletes from tables B and C are synced some time later -- It assumes that there is no way to figure out where the records in A -- are originally sourced from, so both tables are considered delete a from dbo.A a left join ( select RecordID from dbo.B union all select RecordID from dbo.C ) both on both.RecordID = a.RecordID where both.RecordID is null /* records have been deleted from B or C or both */ Both methods may be easily accommodated to handle insert and updates as well. Hope this helps. Oleg [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql
Oleg, I don't have a permission to table C. I just added a column to both A and B tables and thinking to do like this delete from tableA where
Atablename.id <> Btablename.sectionid and atablename.newcolumn = btablename.newcolumn NOte: newcolumn value will be with "TablenameB"
@jangi No, not like that! It looks like you are planning to implement method # 2, i.e. sync records some time after the records from B have already been deleted. If this is the case then you cannot expect atablename.newcolumn = btablename.newcolumn when the records from B have been already deleted (there will be no match by newcolumn for those). You can restate my script based on the following logic: consider only those records from A which have the value in the newcolumn equal to "TablenameB". Join these filtered records with B by ID and require that those records which are still in A but are no longer present in B should be deleted: delete a from Atablename a left join Btablename b on
a.ID = b.sectionid where a.newcolumn = "TablenameB" and b.sectionid is null; This should do it. There is no need to add the **and a.newcolumn = b.newcolumn** to the JOIN condition (most certainly, never to WHERE clause), thouse adding it to the join condition will not hurt anything and would help if there was any possibility for multiple A records with the SAME ID, but this is not the case from what I understand.
@jangi I actually don't quite understand what you said about table C. How is it even possible for you NOT to have a select permission to Table C if all records from it are also present in Table A. so you kinda do have access to the records from table C though you don't have explicit select permission to the table.
19 People are following this question.
