DELETE rows from a specific table

huerita37
huerita37 used Ask the Experts™
on
I need to delete some rows from a table.  I need confirmation of this delete statement.

delete *
from MattersQNVTRST q
where matters in (select matters from matters m where m.Matters = q.Matters and m.AreaOfLaw = 'catrst')

Open in new window


I want to make sure that only the rows in table MattersQnvtrst will be deleted and not the rows in table Matters.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
Yep. The FROM clause is the key. It will target only that table. The fact that you're selecting data from the matters table in a subquery doesn't affect the outer FROM clause. However, the WHERE clause should also refer to the outer table.

The naming is just a little confusing. It appears you have a column named "matters" inside a table named "matters". You also have a column named "matters" inside the table "MattersQNVTRST". It's not a good idea to name columns inside tables the same as the table names themselves.

For absolute clarity I'd rewrite it like this:
delete *
from MattersQNVTRST q
where q.matters in (select m.matters from matters m where m.Matters = q.Matters and m.AreaOfLaw = 'catrst')

Open in new window

It's saying the same thing but it disambiguates some of your names.
SThayaTechnical MAnager
Commented:
try the below query

delete  
from MattersQNVTRST q
where matters in (select matters from matters m where   m.AreaOfLaw = 'catrst')

and

there is a syntax error in your query .." * " will not support in SQL server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial