question

jangi avatar image
jangi 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 :|
tsqlsqlserver
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Could you send a sample of data with expected results, it will be easier for us
0 Likes 0 ·
@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.
0 Likes 0 ·
@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.
0 Likes 0 ·
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.
0 Likes 0 ·
@jangi I just posted the solution with 2 different methods, any should work without the need to identify the source of the record in A (match by ID will suffice). Please let me know if these work for you. Thank you.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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"
0 Likes 0 ·
@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.
0 Likes 0 ·
@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.
0 Likes 0 ·
I will try to do it and I will let you know the result, thanks for your time ( I mean it)
0 Likes 0 ·
Table C and Table B were related to different vendors and they dump data into table A. I work only on Table A and now I got a task update, insert and delete in Table A from Table B.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.