Re: complex problem

Try using 'IN':

delete from data_table
where C2 IN (select N from master
where type='Squirrel');

Also, if I understand your first problem, then this sql statement should swap C1's data with C2's data when C1 is 'Dog' and C2 is 'Squirrel':

update data_table
set c1 = c2,
c2 = c1
where c1 in (select N from master_table where type = 'Dog') and c2 in (select N from master_table where type = 'Squirrel');

-Eric

Paul Acosta wrote:

> I have a data table with two columns which stores numeric
> representations of items described in a master table. Based on the item
> type in the master table I have to switch the columns in the data table.
>
> For example:
>
> Data Table | Master
> C1 C2 | N Type
> 1 2 | 1 Dog
> 3 4 | 2 Cat
> *5 6 | 3 Mongoose
> 1 3 | 4 Squirrel
> *1 4 | 5 Dog
> 6 1 | 6 Squirrel
> 7 1 | 7 Cat
>
> So, for all dogs in column 1 with a squirrel in column 2, I need to swap
> the data in columns 1 and 2. So in my example there are two rows in
> which the columns should be swapped.
>
> I've tried storing all the applicable rows in a new table, and deleting
> them from the original table, with the intention of inserting them
> correctly back into the original table. Only I couldn't get past the
> delete part.
>
> Does anyone know if there is a way to do a delete with a subquery? An
> important note, I've made a view of all dogs in C1, we'll call it view,
> I also need a distinct in my view so I can't just delete from the view.
> Here is an example of what I've tried with no success.
>
> delete from data_table
> where C2=(select N from master
> where type='Squirrel');
>
> unfortunately I receive this message
> ORA-01427: single-row subquery returns more than one row
> which now I understand why that won't work, but I need an alternative,
> if one exists.
>
> Thanks for any help, sorry if this is a bit long and convoluted.
>
> Paul Acosta
Received on Thu May 28 1998 - 15:54:45 CDT