| Author |
Topic |
|
angel9
Starting Member
10 Posts |
 Posted - 2009-04-06 : 16:53:58
|
| Could someone give me some advice on how to remove ALL student records if that student has any credit which is less than 5. Thank you very much in advance. The original table looks like this: ID CREDIT1 22 62 73 83 93 104 114 3Result will look like this (Student ID 1 and 4 are removed from the original table because each student has a credit less than 5): ID CREDIT2 72 83 93 103 11 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
angel9
Starting Member
10 Posts |
Posted - 2009-04-06 : 17:05:49
|
hello Tara,Thank you very much for your quick response. Unfortunately, i don't think that the query you provided will work for students with more than one record. In other word, it will NOT delete the student with ID = 4 as it has a record where credit > 5. quote:
Originally posted by tkizer
DELETE FROM Table1 WHERE ID IN (SELECT ID FROM Table1 WHERE CREDIT < 5)Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
angel9
Starting Member
10 Posts |
Posted - 2009-04-06 : 17:23:17
|
You are absolutely right. it works. However, i am only allowed to use select statement (i can't delete, update any table at all). Is it possible to transfer the query using select statement only? Thanks again!Your help is greatly appreciated!quote:
Originally posted by tkizer
Yes it will. Please try it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Amber99
Starting Member
11 Posts |
Posted - 2009-04-06 : 20:41:27
|
| SELECT ID, CREDIT FROM YOURTABLESNAME WHERE CREDIT<5 ORDER BY CREDIT ASC;(YOURTABLENAME is basically whatever the name of the table is... now if the number are stored as TEXT then you would need ' ' around the 5 like this:SELECT ID, CREDIT FROM YOURTABLESNAME WHERE CREDIT<'5' ORDER BY CREDIT ASC;but im sure the 1st one will work fine... ORDER BY is to have CREDIT by Ascending order as you have indicated in the result. Thanks!Amber- |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 10:27:11
|
quote:
Originally posted by Amber99
SELECT ID, CREDIT FROM YOURTABLESNAME WHERE CREDIT<5 ORDER BY CREDIT ASC;(YOURTABLENAME is basically whatever the name of the table is... now if the number are stored as TEXT then you would need ' ' around the 5 like this:SELECT ID, CREDIT FROM YOURTABLESNAME WHERE CREDIT<'5' ORDER BY CREDIT ASC;but im sure the 1st one will work fine... ORDER BY is to have CREDIT by Ascending order as you have indicated in the result. Thanks!Amber-
it will wont work . it will still return 4 11 which is not what OP want |
|
|
|
|
query help