Archives of the TeradataForum

Message Posted: Wed, 30 Jun 2005 @ 00:12:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The reason the query doesn't work is because the syntax is wrong. I would refer you to the SQL reference manuals. Specifically, look at the Delete statement. You will notice that there is no order by or sample clause available.

The Order by and sample clauses of the select statement are used to control how data is returned to the user. As such, they only apply to the select statement. Specifically the "order by 1" clause sorts the data according to the values in column 1. The Sample 50 clause picks records at random from the set of available records. The sorted randomly picked rows are returned for presentation to the user.

There is little value in sorting records on a delete. In other words what difference does it make if the record that is deleted is at the front, middle or back of the list of sorted records - just so long as the right one is deleted.

As for the sample clause, most people don't won't to delete 50 records at random. Most people want to delete specific records by key values - or they want to delete all records in the table.

You will notice that the delete statement really only includes a where clause.

If you truly want to delete 50 rows at random, you will need to create another table with the key values of the 50 randomly selected rows. Then use this table in the where clause of your delete.

For example:

     Create table randomkeys as (
         Select col1, col2, col3
         From table_name
         Sample 50
         ) with data;

     Delete from some_table
     Where (col1, col2, col3) in (
         Select col1, col2, col3
         From randomkeys
         );

I suspect that this is not what you want to do - but in the absence of a stated goal, it is difficult to suggest an actual solution.

I hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>