Delete Link - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete Link

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-07-12 : 06:15:21

Hi I am tring to delete all data from a sub entity table and link table where a certain date range is <= to the one below.

Here is the select query that gets the data i want to remove. I just need to turn it into a delete query. I have tried several example but can't get it working. Any ideas would be great. Thanks for looking.


select top 10 notes, createtime from wce_history  h join wce_linkto l on h.uniqueid = l.luniqueid where createtime <= '1980/01/01'

Sachin.Nand

2937 Posts

Posted - 2010-07-12 : 06:39:25

Use a derived table.Something like this



delete t from
(
select top 10 notes, createtime from wce_history 
 h join wce_linkto l on
 h.uniqueid = l.luniqueid where createtime <= '1980/01/01'
)t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-07-12 : 06:46:15

Thanks for the feedback, just tried running that and got an error:

It looks like the derived option is being overly careful. Any other thoughts on that?

Msg 4405, Level 16, State 1, Line 3
View or function 't' is not updatable because the modification affects multiple base tables.

Sachin.Nand

2937 Posts

Posted - 2010-07-12 : 06:57:19

Try this one.



Delete t from
(
Select * from yourtable1 where exists

  (
     Select * from yourtable2 where yourtable1.id=yourtable12.id
   )
)t

Make sure you do this in a test enviroment.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-07-12 : 07:24:04

HI,

Ok, after some testing something is not right here. I ran some tests on a copy of the production tables. I would have expected the wce_history table and the wce_linkto table to delete the amount of rows from the first query, 1318. It only removed 659 rows with the final delete query.

Am i getting something wrong?

Thanks for the help.

After runnning each query below separately here are the results.



select count(*)from wce_history  h join wce_linkto l on h.uniqueid = l.luniqueid where createtime <= '1999/02/01'

Above query results = 1318 rows


select count(*) from wce_history

Above query results = 1618372 rows


select count(*) from wce_linkto where lutablename = 'wce_history'

Above query results = 1955537 rows


Delete t from
(
Select * from wce_history where exists

  (
     Select * from wce_linkto where wce_history.uniqueid=wce_linkto.luniqueid and createtime <= '1999/02/01'

   )
)t
[/code]
[/code]
Above query results = 659 rows

Sachin.Nand

2937 Posts

Posted - 2010-07-12 : 07:36:38

No its not possible to delete data from multiple tables.However you can set delete behaviour to cascade and that will delete children records when a parent record is deleted.
Also I gave you the exists query as an alternative because you had a top clause in the your delete query.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-07-12 : 09:48:45

Why do they not make that possible? It seems like simpel logic. Anyone else hit this issue?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 11:18:00

createtime belongs to which table?

something like this maybe? of course you will need to wrap this in a batch transaction so in case parent delete fails you abort transaction to avoid orphaned records


DECLARE @MyTableVar table (
    uniqueidint NOT NULL);

DELETE wce_history 
OUTPUT DELETED.uniqueid
  INTO @MyTableVar
FROM wce_history AS wh
WHERE wh.createtime <= '1999/02/01'

DELETE wce_linkto
FROM wce_linkto wl
INNER JOIN @MyTableVar tv
   ON wl.uniqueid = tv.luniqueid

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-07-12 : 11:36:20

Thanks, the createtime is in the history table. I'll give this a go in a bit. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources