question

andik avatar image
andik asked

delete (use inner join) from 2 table and relation by kode_reg is not match

i have 2 table and i want delete record from 2nd table is not match can you give some answer for me. thanks before.

andik

deleterecord
10 |1200 characters needed characters left characters exceeded

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

Joe Celko avatar image
Joe Celko answered

Look up the EXCEPT operator.

SELECT * FROM Foo EXCEPT [ALL] SELECT * FROM Bar

10 |1200 characters needed characters left characters exceeded

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

Madhivanan avatar image
Madhivanan answered

See if this gives the data to be deleted

select t1.* from table1 as t1 where not exists(            
select * from table2 as t2 where t1.keycol=t2.keycol)            

if you get required data to be deleted

delete t1 from table1 as t1 where not exists(            
select * from table2 as t2 where t1.keycol=t2.keycol)            
10 |1200 characters needed characters left characters exceeded

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

TG avatar image
TG answered

Not sure why you need to use "inner join" but if you can relax that requirment: Here's an alternative to Madhivanan's solution. Sometimes this technique will out-perform the "where not exists" method:

select t2.*            
--delete t2            
from table2 as t2            
left outer join table1 as t1            
       on t1.<PK_cols> = t2.<PK_cols>            
where  t1.<PK_cols> is null            
10 |1200 characters needed characters left characters exceeded

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

Benjamin avatar image
Benjamin answered

I think you mean equi-join not INNER JOIN when you in fact you need an OUTER JOIN. When you join on a key and filter by a NULL value in that key, you are isolating the records which do NOT exist in the other table. Here are the tricks to writing a DELETE with JOIN clause to remove rows in a table which are not present in another table.

  • use an OUTER JOIN (left or right)
  • join ON the key field(s)
  • filter on one of the key field(s) in the WHERE clause as being NULL
  • NOTE: you must use the table alias to specify which table to affect DELETE FROM becomes DELETE TableAlias FROM


--this first code block is all you need to do make it work... if you want an end-to-end example, see the additional code blocks below

DELETE t2 FROM            
    [dbo].[Table1] t1            
RIGHT JOIN             
    [dbo].[Table2] t2            
ON            
    t1.[kode_reg] = t2.[kode_reg]            
WHERE            
    t1.[kode_reg] IS NULL            

--create Table1 and Table2 for the test

CREATE TABLE [dbo].[Table1] (            
    [kode_reg] int NOT NULL )            
CREATE TABLE [dbo].[Table2] (            
    [kode_reg] int NOT NULL )            

--insert 2 records into Table1

INSERT INTO [dbo].[Table1] ([kode_reg]) VALUES (1)            
INSERT INTO [dbo].[Table1] ([kode_reg]) VALUES (2)            

--insert 3 records into Table 2

INSERT INTO [dbo].[Table2] ([kode_reg]) VALUES (1)            
INSERT INTO [dbo].[Table2] ([kode_reg]) VALUES (2)            
INSERT INTO [dbo].[Table2] ([kode_reg]) VALUES (3)            

--SELECT the records to be deleted

SELECT t2.* FROM            
    [dbo].[Table1] t1            
RIGHT JOIN             
    [dbo].[Table2] t2            
ON            
    t1.[kode_reg] = t2.[kode_reg]            
WHERE            
    t1.[kode_reg] IS NULL            

--DELETE the records in Table2 that are not found in Table1

DELETE t2 FROM            
    [dbo].[Table1] t1            
RIGHT JOIN             
    [dbo].[Table2] t2            
ON            
    t1.[kode_reg] = t2.[kode_reg]            
WHERE            
    t1.[kode_reg] IS NULL            

--clean up tables from test

DROP TABLE [dbo].[Table1]            
DROP TABLE [dbo].[Table2]            
10 |1200 characters needed characters left characters exceeded

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

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.