TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
278.3k
How to use join without using sub query on delete
Apr 13 2020 5:16 PM
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
Reply
Answers (
4
)
Convert Pastel .dat Data Files
How get conflicted part that have same status two time based