simple sql - 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

Transact-SQL (2000)

simple sql

Author

Topic

lkytmr
Starting Member

14 Posts

Posted - 2002-11-19 : 10:55:29

I know this is simple, but I am an infrequent user and cannot figure this one out.

Table a
field myword
field other fields

Table b
field myword
no other fields

Want to delete all entries in table a where a.myword = b.myword.

b.myword is a small subset of a.myword. For every b.myword, a.myword does exist but not the reverse.

I have tried joins with a select and this works but when I substitute the word delete for select, it fails.

Thanks for help.

Tom Ryan

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-19 : 11:02:43

DELETE TableA
FROM TableB
WHERE TableA.myword = TableB.myword

lkytmr
Starting Member

14 Posts

Posted - 2002-11-19 : 13:00:26

I don't follow.
The tables are defined as 'a' and 'b'.

Do you mean "Delete a from b where a.myword = b.myword"

or "Delete a.myword from b where a.myword=b.myword"

or "Delete a.myword from b.myword where a.myword=b.myword"

It seems to me that a simple

"Delete * from a where a.myword=b.myword"

should work but it doesn't. It seems to want to treat b.myword as a parameter.

What do you think?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-19 : 13:06:40

>> Delete * from a where a.myword=b.myword

you need to tell it you are going to access b so

delete a from b where a.myword=b.myword
or you can code it
delete from a from b where a.myword=b.myword

but not delete * from...
look at the syntax for delete in the bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-19 : 13:08:37

Needs a little tweaking:

DELETE TableA
FROM TableA INNER JOIN TableB ON TableA.myword = TableB.myword

The syntax for DELETE...FROM is fully detailed in Books Online. You could also use the WHERE syntax as before, but it's probably better to use the INNER JOIN syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources