DELETE Stmt Help!!! - 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 2000 Forums

SQL Server Development (2000)

DELETE Stmt Help!!!

Author

Topic

Jothikannan
Starting Member

36 Posts

Posted - 2006-01-25 : 08:56:40

hi,

Delete tst1
from tst1 a(nolock)
where a.col1=2

The above delete Stmnt Deleting only rows where "col1=2" in table tst1
but the query donot have any joins between tst1 and alias a

How it is deleting only col1=2 ,instead of deleting all rows in tst1??

Plz, Can Anyone help me !?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 08:59:30

The query

Delete tst1
from tst1 a(nolock)
where a.col1=2

is equivalent to

Delete
from tst1 (nolock)
where col1=2

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-25 : 08:59:59

"a" is an alias for "tst1" in the above example. So no second table is required.

Perhaps consider the more longhand version:

Delete tst1
from tst1 AS a(nolock)
where a.col1=2

However, having said that, I would have expected all references to "tst1" to need changing to "a" - i.e.

Delete ~~tst1~~ a

Kristen

Jothikannan
Starting Member

36 Posts

Posted - 2006-01-25 : 09:24:36

see, the following query

Delete
tst1
from
tst2 a(nolock)
where
a.col1=1

In "from " i have used table "tst2"

if a.col1=1 is true in table "tst2",it is Deleting all rows in "tst1"

and if a.col1=1 is false in table "tst2",it is Deleting Zero rows in "tst1"

the above activity is fine,
but i m expecting the same behaviour for the previous Query .
But it is Deleting abnormally. so it is confusing!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:28:14

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

Jothikannan
Starting Member

36 Posts

Posted - 2006-01-25 : 09:37:45

thnks madi...

Create table tst1 (col1 int)

insert tst1 select 1
insert tst1 select 2
insert tst1 select 3

Create table tst2 (col1 int)

insert tst2 select 4
insert tst2 select 5
insert tst2 select 6

Begin Tran

Delete tst1
from tst2 a(nolock)
where
a.col1 = 5

rollback tran

the above deleting 3 rows ,ok thats fine

Begin Tran

Delete tst1
from tst2 a(nolock)
where
a.col1 = 1

rollback tran

the above deleting zero rows ,ok thats fine

Begin Tran

Delete tst1
from tst1 a(nolock)
where
a.col1 = 2

rollback tran

Here is my problem ,it should delete 3 records

why it deleting 1 record?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 10:53:20

The last record is deleting only the 1 row because it is matching the criteria to the 2nd row in table tst1, the rest are deleting all rows in tst1 because the where clause is basically trying to do a logical operation rather than a true test of the data due to a lack of referential constraints..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 05:04:05

There are no relations between the tables. As said there should be referencial integrity

Madhivanan

Failing to plan is Planning to fail

Jothikannan
Starting Member

36 Posts

Posted - 2006-01-27 : 10:09:32

tnk Ricky & Madi

i m Still getting Confusing

is any syntax available for Update like this?

how its putting Proper join b/w 2 tables?

Pls.. Help ME

Kristen
Test

22859 Posts

Posted - 2006-01-28 : 02:06:27

What I normally do is to update an Alias, rather than a table name.

So I would do:


UPDATE U
SET MyColumn = 'foo'
FROM MyTable AS U
WHERE MyOtherColumn = 'bar'


UPDATE U
SET MyColumn = 'foo'
FROM MyTable1 AS U
     JOIN MyTable2 AS T2
          ON T2.MyPrimaryKey = U.MyPrimaryKey
WHERE MyOtherColumn = 'bar'

I find this easy to read, and easy to change from single table to multi-table JOIN if/when I need to.

I always use U for the update table alias, so there is no chance of me accidentally using a tablename instead of an alias, etc.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources