November 8, 2016 at 3:18 pm
Hi,
I want to delete temp table by joining dervied table.
EX:
; with cte
(
select * from employee
)
delete #temp
from #temp a
inner join a.id=cte.id
November 8, 2016 at 3:27 pm
aliases are your friend, especially with a delete construct like this;
here's an example:
; with cte
AS
(
select * FROM employee
)
delete myTarget
from #temp myTarget
inner join cte MySource ON myTarget.id = MySource.id
Lowell
November 8, 2016 at 3:38 pm
without Cte i want to delete
November 8, 2016 at 4:48 pm
@Patrick123 (11/8/2016)
without Cte i want to delete
Why? It does the job well.
Try this.
delete a
from #temp a
inner join employee e on a.id = e.id
-- Put the selection criteria that appeared in your cte below, or else
-- you will delete all #temp rows that match id with any employee.
-- If you WANT to delete all rows that match an employee id, omit the WHERE.
where e.[columnX] = something
Or, if your instructor is being picky about the definition of a "derived table"
delete a
from #temp a
join (select id f
from employee
where ?? ) e (ID) on a.id = e.id
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 8, 2016 at 5:35 pm
@Patrick123 (11/8/2016)
Hi,I want to delete temp table by joining dervied table.
EX:
; with cte
(
select * from employee
)
delete #temp
from #temp a
inner join a.id=cte.id
Or here's another version with a semi-join:
delete t
from #temp t
where exists (select 1 from employee e where e.id = t.id)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply