| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: complex problem
A copy of this was sent to Paul Acosta <pacosta_at_lctx.com>
(if that email address didn't require changing)
On Thu, 28 May 1998 14:57:29 -0500, you 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.
>
update data_table
set c1 = c2, c2 = c1
where c1 in ( select N from master where type = 'Dog' )
and c2 in ( select N from master where type = 'Squirrel' )
would do the update in place...
>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');
>
Well, in your example, for whatever reason, squirrel exists 2 times in MASTER. C2 = ( 4, 6 ) does not compute. You could:
delete from data_table
where c2 IN ( 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
>
>
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 28 1998 - 15:35:27 CDT
 |
 |