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