I want to delete those data from tables Orders and customers which is not selecting in below join

Question

Post reply

I want to delete those data from tables Orders and customers which is not selecting in below join

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

forsqlserver (7/23/2012)
It is giving error:
Msg 156,state 1,Line 6
Incorrect syntax near the keyword except.

You are probably using SQL Server 2000

EXCEPT operator works only in SQL Server 2005+

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

forsqlserver SSCoach Points: 18902 More actions · Answer 2

I am using 2008 R2 Std edtn.

I think it is giving error due to brackets..

Thanks

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

forsqlserver (7/23/2012)
I am using 2008 R2 Std edtn.
I think it is giving error due to brackets..

Indeed. Another version:

;with Remove

as (

select a.c1

from t1580 a

except

select a.c1

from T1580 a

inner join T2115 b on a.C301289100 = b.C1000000161

and b.C7 < 5

)

delete a

from t1580 a

join Remove on a.c1 = Remove.c1

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

forsqlserver SSCoach Points: 18902 More actions · Answer 4

I have lakh of rows to be deleted in this table it will full my log file...

I want to use top in delete statement..

Thanks

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

forsqlserver (7/23/2012)
I have lakh of rows to be deleted in this table it will full my log file...
I want to use top in delete statement..

It's about time you started thinking for yourself. Tell us what you have tried.

Slowly drip-feeding your requirements is unhelpful and wastes people's time.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

forsqlserver (7/23/2012)
I have lakh of rows to be deleted in this table it will full my log file...
I want to use top in delete statement..

whats the total number of rows in the table and how many need to be deleted?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

forsqlserver (7/23/2012)
I have lakh of rows to be deleted in this table it will full my log file...
I want to use top in delete statement..

Please read the following article, it may help you write the query (queries) you need to delete the records and control the size of the transaction log.

http://www.sqlservercentral.com/articles/T-SQL/67898/

forsqlserver SSCoach Points: 18902 More actions · Answer 8

I want to do this actually this

===================================================

with Remove

as (select X.id1 from X

except

select X.id1 from X inner join Y on X.id2 = Y.id2)

DECLARE @BatchSize INT

SET @BatchSize = 1000

WHILE @BatchSize <> 0

BEGIN

Delete top(@BatchSize) X from X join Remove on X.id1 = Remove.id1;

SET @BatchSize = @@rowcount

END

================================================

can anyone correct it...i have added declare and while loop in the below code:

Please help to run above code itis not executing.

==============================================

with Remove

as (select X.id1 from X

except

select X.id1 from X inner join Y on X.id2 = Y.id2)

Delete top(1000) X from X join Remove on X.id1 = Remove.id1;

=============================================

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

forsqlserver (7/25/2012)
I want to do this actually this
===================================================
with Remove
as (select X.id1 from X
except
select X.id1 from X inner join Y on X.id2 = Y.id2)
DECLARE @BatchSize INT
SET @BatchSize = 1000
WHILE @BatchSize <> 0
BEGIN
Delete top(@BatchSize) X from X join Remove on X.id1 = Remove.id1;
SET @BatchSize = @@rowcount
END
================================================
can anyone correct it...i have added declare and while loop in the below code:
Please help to run above code itis not executing.
==============================================
with Remove
as (select X.id1 from X
except
select X.id1 from X inner join Y on X.id2 = Y.id2)
Delete top(1000) X from X join Remove on X.id1 = Remove.id1;
=============================================

When you say it isn't executing, what is it doing? Are you getting some kind of error message(s)? If so, we need to know. Just telling us it isn't running doesn't tell us a thing.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Pretty sure you were getting syntax errors.

Try the following:

DECLARE @BatchSize INT

SET @BatchSize = 1000;

WHILE @BatchSize <> 0

BEGIN

with Remove as (

select

X.id1

from

X

except

select X.id1

from

X

inner join Y

on X.id2 = Y.id2)

Delete top(@BatchSize) X

from

X

join Remove

on X.id1 = Remove.id1;

SET @BatchSize = @@rowcount;

END