delete rows using except ?

Found out that except is very fast, but is it possible to delete rows using except ?

Like:

delete accountnumber, central
from table1
except
select accountnumber, central
from table 2

Jam:

The DELETE FROM ... WHERE NOT EXISTS is likely to be faster than a DELETE FROM ... FROM ... EXCEPT; something such as:

delete from table1
from table1 a
where not exists
( select 0 /* a dummy */ from table2 b
where a.accountNumber = b.accountNumber
and a.central = b.central
)

Dave

|||

Hmmmm. This query seems to have the same amount of logical IO as the previous query:

delete from table1
from table1 a
inner join
( select accountNumber,
central
from table1
except
select accountNumber,
central
from table2
) b
on a.accountNumber = b.accountNumber
and a.central = b.central

I'll do a little more digging. At the moment, this query also looks viable.

Dave

|||

Sorry about that, I was simultaneously dealing with an implementation problem. Really, I cannot go any farther with my mock-up without knowing specifics about your actual implementation because your implementation will have a big impact on the execution plan. Yeah, I tested out with the same amount of logical IOs with both queries but this really is more qualitative than anything and doesn't prove too much.

I can say little more than the qualitative viability of the EXCEPT query; however, I don't consider this a simpler query than the NOT EXISTS query. Can you give more information about your two tables -- especially index information?

Dave

Delete records from subscriber only

I have on distributed enviornment in which data is collected by remote clients and send to the central publisher using merge publication and filters. I need to purge old data from the subsriber and not in the server. How do I do this?
Thanks
Anand,
what if some of the old data is modified on the publisher in future, or
perhaps this is not possible? If it is then you'll have a problem. You could
leave all the data as it is on the subscriber and use views to effectively
remove it for the front-end application. If you need the name to be the same
on the subscriber as the publisher, you could replicate to a different
tablename on the subscriber and use a view name equal to the tablename on
the publisher. Of course this would require reinitializing though. Finally,
you could drop the subscription, add a filter then reinitialize.
HTH,
Paul Ibison
|||The data at the server won't change for those records, if it did and it is replicated to the subscriber occasionaly it won't be a problem. I was trying to find out whether there is a way not to replicate deletes in merge publication (Easily) without dropi
ing the subscription.
Thanks for input
Anand
|||Anand,
there is another solution - you could modify the merge replication triggers
so as to not register your deletes in MSmerge_tombstone, although this would
lead to an unsupported solution.
HTH,
Paul Ibison
|||the cleanest way to do this is using transactional replication with custom
stored procedures. Do you require tranasactions that require on the
subscriber to be replicated to the publisher?
"Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:4FB33D2F-1310-4125-86A0-21EDB56E6388@.microsoft.com...
> I have on distributed enviornment in which data is collected by remote
clients and send to the central publisher using merge publication and
filters. I need to purge old data from the subsriber and not in the server.
How do I do this?
> Thanks
|||Yes. I have already setup a merge replication with 30 odd disconnected clients replicating to a central server. Most of the transactions actually happen at the clients. It is not possible for me change the replication model now. Is there a way to script a
nd tell the clients not to replicate deletes to the publisher.
Anand
|||Can we use alter table to disable all triggers on a table and delete the records and enable triggers. Will this work?
Thanks
Anand
|||Well I tried disabling the triggers, but you can not alter the tables which are replicated. I stoped the SQL server agent and deleted the msmerge_tombstone records after the delete on the subscriber and restarted the SQL server agent. It seems to work the
way I want it. Any caveats?
Thanks
Anand