How to delete using a subquery

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 2005 Forums

Other SQL Server Topics (2005)

How to delete using a subquery

Author

Topic

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-14 : 15:45:51

Hi I got a table 'inventory' from which i need to delete all the records,but some,which are having a 'process_level<8',which is a field in another table called 'course'.The foreign key is 'course_name' and 'course_number' combined.Can anyone help me with the syntax for this?
Thanks
GEORGE

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-14 : 15:54:19

DELETE FROM [ITDEdb].[dbo].[dbt_Course_Inventory]
WHERE (( course_number=
all(select course_number
from [ITDEDEV].[dbo].[dbt_course2007]
where process_level=8)) and
(course_name=all(select prefix
from [ITDEDEV].[dbo].[dbt_course2007]
where process_level=8)))

This is the query which i tried.But itz not working

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-14 : 16:25:26

Assuming you want process_id >= 8 since you said less than 8. I am making assumptions here but it seems to be what you were asking.

DELETE
FROM ITDEdb.dbo.dbt_Course_Inventory
WHERE Course_Number IN
(SELECT course_number
FROM ITDEDEV.dbo.dbt_course2007
WHERE process_level >= 8 )
OR Course_Name IN
(SELECT Course_Name
FROM ITDEDEV.dbo.dbt_course2007
WHERE process_level >= 8 )

Future guru in the making.

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-14 : 16:39:21

Actually i need to delete all records from course_inventory table,where the process_level=8.
And the foreign key should be course_number and course_name combined.I hope u mite be able to help me in this.
Thanks
GEORGE

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-14 : 16:43:58

quote:
Originally posted by georrgetjojo

Actually i need to delete all records from course_inventory table,where the process_level=8.
And the foreign key should be course_number and course_name combined.I hope u mite be able to help me in this.
Thanks
GEORGE

Can you post the ddl for both tables?

Future guru in the making.

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-14 : 16:53:11

the columns of inventory table are:inventory_id,course_name,course_number,hours.
and for the other table is course_number,prefix,process_level,hours

here prefix in the second table is the same as the course_name of the first one and also there could me same course_number under different course_names.so we have to combine both the columns as the foreign key.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-14 : 17:06:24

You should be using joins in your delete command instead of subqueries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-14 : 18:58:16

quote:
Originally posted by tkizer

You should be using joins in your delete command instead of subqueries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I understand the point of using joins rather then subqueries but based on what I understand it doesn't make a difference since it is treated as a join by SQL Server anyway.

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-14 : 19:29:45

quote:
Originally posted by georrgetjojo

the columns of inventory table are:inventory_id,course_name,course_number,hours.
and for the other table is course_number,prefix,process_level,hours

here prefix in the second table is the same as the course_name of the first one and also there could me same course_number under different course_names.so we have to combine both the columns as the foreign key.

Try this out:

DELETE
FROM TDEdb.dbo.dbt_Course_Inventory
WHERE Inventory_ID IN
(SELECT Inventory_ID
FROM TDEdb.dbo.dbt_Course_Inventory A
JOIN ITDEDEV.dbo.dbt_course2007 as B
ON A.Course_Number = B.Course_Number
AND A.Course_Name = B.Prefix
AND B.process_level = 8
)

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-14 : 23:52:11

quote:
Originally posted by Zoroaster

quote:
Originally posted by tkizer

You should be using joins in your delete command instead of subqueries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I understand the point of using joins rather then subqueries but based on what I understand it doesn't make a difference since it is treated as a join by SQL Server anyway.

Future guru in the making.

You would have to compare execution plans in order to determine that. From there you'll see how SQL Server treated the subquery.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-17 : 12:27:41

But when i am using the first method,no rows are getting affected.but whn i am using the table joins,more rows are getting effected,than expected.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-17 : 12:30:53

this will help you:
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 12:40:50

quote:
Originally posted by georrgetjojo

But when i am using the first method,no rows are getting affected.but whn i am using the table joins,more rows are getting effected,than expected.

I haven't seen a join solution posted here, so could you post the join solution that you are using that is affected more rows than expected?

Also, it's very hard to figure out what you want here as you haven't provided sample data for us to look at. Please post some sample rows from all tables involved and show us which rows need to get deleted and which ones don't.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-17 : 14:36:48

DELETE
FROM TDEdb.dbo.dbt_Course_Inventory
WHERE Inventory_ID IN
(SELECT Inventory_ID
FROM TDEdb.dbo.dbt_Course_Inventory A
JOIN ITDEDEV.dbo.dbt_course2007 as B
ON A.Course_Number = B.Course_Number
AND A.Course_Name = B.Prefix
AND B.process_level = 8
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 14:42:09

That's not what I meant when I mentioned you should use a join solution. What I was referring to was getting rid of the entire subquery and using only joins to access the other table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-17 : 14:47:01

i_id c_name c_number CIP_code course_title
2104 AGED 574 1313010005 ASSESSMENT &EVALUATION
2184 ART 404 5007030003 CONTEMPORARY ISSUES
2262 BLED 402 1302010004 BIL ORAL LANG
2339 CHEM 202 4005040002 ORG PROB SOL II 3

This is the sample records of the course inventory table.

c_id prefix c_number process_level CIP_code
200 AGED 574 8 1313010005
201 ART 404 4 5007030003
203 BLED 402 8 1302010004

so this mite be the second table and i need to delete the records from the first table where the process level is 8.

Hope you would be able to help
thanks

georrgetjojo
Starting Member

10 Posts

Posted - 2007-09-17 : 14:50:01

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 14:54:27

DELETE t1
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.c_name = t1.prefix AND t2.c_number = t1.c_number
WHERE t2.ProcessLevel = 8

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 14:55:11

DELETE ci
FROM dbt_Course_Inventory ci
INNER JOIN dbt_course2007 c
ON ci.Course_Number = c.Course_Number AND ci.Course_Name = c.Prefix
WHERE c.process_level = 8

To see if this will work without actually running it, run this:

SELECT *
FROM dbt_Course_Inventory ci
INNER JOIN dbt_course2007 c
ON ci.Course_Number = c.Course_Number AND ci.Course_Name = c.Prefix
WHERE c.process_level = 8

I wasn't sure about the join condition (ON), so please correct that if needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources