Loading

How to use join without using sub query on delete

How to use join without using sub query on delete

ahmed elbarbary

6y964Databases & DBA

I work on sql server 2012 I need use join or any way without using subquery

to delete records

on this query i delete from trade code that have childcodetype and childcodevalue

without have parentcode type and parent code value

so row number 5 and 6 will be deleted

drop table #MappingCodeValue
drop table #TradeCode
create table #MappingCodeValue
(
id int identity (1,1),
ParentCodeType nvarchar(50),
ParentCodeValue nvarchar(50),
ChildCodeType nvarchar(50),
ChildCodeValue nvarchar(50)
)
INSERT INTO #MappingCodeValue
(ParentCodeType,ParentCodeValue,ChildCodeType,ChildCodeValue)
VALUES
('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),
('ECCS-US','AB778-US','ECCS-URB','AB778-URB')
CREATE TABLE #TradeCode
(
TradeCodeId int identity(1,1),
PartId int,
CodeType nvarchar(50),
CodeValue nvarchar(50)
)
insert into #TradeCode(PartId,CodeType,CodeValue)VALUES
(1222,'ECCS-US','AB123-US'),
(1255,'ECCS-US','AB555-US'),
(1222,'ECCS-URB','AB123-URB'),
(1255,'ECCS-URB','AB555-URB'),
(1444,'ECCS-URB','AB666-URB'),
(1931,'ECCS-URB','AB778-URB')
delete t
from #tradecode t
where
not exists
(select 1 from #mappingcodevalue pn
where
(t.CodeValue = pn.ParentCodeValue and t.CodeType = pn.ParentCodeType) or
( (t.CodeValue = pn.ChildCodeValue and t.CodeType = pn.ChildCodeType) and
(exists(select 1 from #TradeCode p
where p.CodeValue = pn.ParentCodeValue and p.CodeType = pn.ParentCodeType)) ))

TradeCodeId PartId CodeType CodeValue
1 1222 ECCS-US AB123-US
2 1255 ECCS-US AB555-US
3 1222 ECCS-URB AB123-URB
4 1255 ECCS-URB AB555-URB
5 1444 ECCS-URB AB666-URB
6 1931 ECCS-URB AB778-URB

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ronika Jency6y ago
Accepted answer
Hi, Please use below query to delete

delete from #TradeCode where PartId
not in (select t.PartId from #TradeCode t inner join #MappingCodeValue m
on t.CodeType = m.ParentCodeType and t.CodeValue = m.ParentCodeValue
where
(t.CodeValue = m.ParentCodeValue and t.CodeType = m.ParentCodeType) or
(t.CodeValue = m.ChildCodeValue and t.CodeType = m.ChildCodeType)
)
0
Vinay Singh6y ago
Hi
you can try this
DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server
https://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition
0
Ronika Jency6y ago
Hi,

you can use join also. Inner Join and Join are functionally equivalent. but INNER JOIN more readable, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS).

As you said, My query executed successfully. Please accept my answer.
0
ahmed elbarbary6y ago
Thank you for reply
really it is good
and give me correct result
but can i write code using direct join or not
ex
select from x join y like that or not
can i write by using join if possible please tell me
0