Ahh... ok.. Thanks!

PL

>At 11:38 AM 5/03/2005 +1100, you wrote:
>
>
>
>>Hi Everybody,
>>
>>I'm just wondering why this query doesn't delete anything?
>>
>>DELETE FROM G_USER U
>>WHERE U.USER_NUM NOT IN (SELECT FIRST 3 USER_NUM FROM G_USER)
>>
>>
>
>It doesn't delete anything because it's looking at the entire
>table. SELECT FIRST n can't be used as a search criterion, since it
>operates on the entire output set. Your statement is just saying "delete
>anything that is not in the table".
>
>
>
>
>>The G_USER table has more than three records... if I remove the not from
>>the where clause, all the records get deleted, as expected. Basically,
>>I'm just wanting to ensure that the table g_user has only three records
>>in it.
>>
>>
>
>Well, SQL doesn't expect to be asked to delete all records except some
>random "any 3". You'll need to get the PKs of the rows you want to keep,
>and explicitly exclude them, viz.
>
>delete from g_user
> where user_id <> 1 and user_id <> 2 or user_id <> 3
>
>or whatever.
>
>./hb
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>

--
Peter Lee ptle@...
-----------------------------------------------------------------------
Rising Software Australia Pty. Ltd. http://www.risingsoftware.com/
Publishers of 'Auralia' - Ear Training and 'Musition' - Theory Training
Ph: +61 3 9481 3320 FAX: +61 3 9481 3380 USA Freecall: 1 888 667 7839