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?ThanksGEORGE |
|
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. DELETEFROM ITDEdb.dbo.dbt_Course_InventoryWHERE 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.ThanksGEORGE |
|
|
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.ThanksGEORGE
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,hourshere 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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,hourshere 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:DELETEFROM TDEdb.dbo.dbt_Course_InventoryWHERE 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 |
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
georrgetjojo
Starting Member
10 Posts |
Posted - 2007-09-17 : 14:36:48
|
DELETEFROM TDEdb.dbo.dbt_Course_InventoryWHERE Inventory_ID IN(SELECT Inventory_IDFROM TDEdb.dbo.dbt_Course_Inventory AJOIN ITDEDEV.dbo.dbt_course2007 as BON A.Course_Number = B.Course_NumberAND A.Course_Name = B.PrefixAND 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 helpthanks |
|
|
georrgetjojo
Starting Member
10 Posts |
Posted - 2007-09-17 : 14:50: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 helpthanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-17 : 14:54:27
|
DELETE t1FROM Table1 AS t1INNER JOIN Table2 AS t2 ON t2.c_name = t1.prefix AND t2.c_number = t1.c_numberWHERE 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 ciFROM dbt_Course_Inventory ciINNER JOIN dbt_course2007 cON ci.Course_Number = c.Course_Number AND ci.Course_Name = c.PrefixWHERE c.process_level = 8To see if this will work without actually running it, run this:SELECT *FROM dbt_Course_Inventory ciINNER JOIN dbt_course2007 cON ci.Course_Number = c.Course_Number AND ci.Course_Name = c.PrefixWHERE c.process_level = 8I wasn't sure about the join condition (ON), so please correct that if needed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|