Help with INSERT INTO - SELECT problem

[patrickdrd]

Good Morning to everyone!

I want to insert into a table using the following select:

SELECT DECODE(A.e02_code, '045934', '045935') e02_code, A.*
FROM TESTC A
WHERE A.e02_code = '045934'

My problem is that I have one more column in the select query (the table I want to insert into has the same columns as TESTC table).
I do not want to supply each column separately, just want to cut off the e02_code column from the result set and use the decode instead.

Is this possible? And, if yes, how?

Thanks in advance!

 

[LKBrwnDBA]

No. The SELECT has to provide the same number of columns as the INSERT requires.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[patrickdrd]

Of course, I know, but is there any other way to solve this problem?

A partner of mine suggested that we could solve this using temporary tables to edit the data. Is this possible? If so, what about users accessing the same temporary table?
What about sessions and concurrent transactions?

 

[LKBrwnDBA]

Using temporary tables requires a minimun of two statements, a CREATE TABLE TEMPX AS SELECT DECODE... and the INSERT.

And, you would still have to specify ALL the required columns either for the INSERT or for the CREATE.






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[patrickdrd]

That's not a problem, I could filter out the decode column from the query and "construct" that by updating the temporary table.

But, what about concurrent transactions?

 

[LKBrwnDBA]

You would still have to supply each column separately.

What do you want to know about concurrent transactions?

Maybe you need to let us know the complete requirements and/or an example of what you want to accomplish.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[patrickdrd]

I want to be able to get data from testc table,
change only one column (e02_code - that's the purpose of decode) and insert them into testc with the new e02_code.

 

[SantaMufasa]

Patrick,

It appears to me that you want in the target table just the rows from TESTC table "...WHERE A.e02_code = '045934' ", and you want the resulting value of column "e02_code" to be '045935', correct?

If so, then how about this code:

INSERT INTO Patrick (SELECT * from TESTC WHERE e02_code = '045934');
UPDATE Patrick set e02_code = '045935';
COMMIT;

Let us know of any problems that this code presents.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[patrickdrd]

quite close, but I want to do this on the same table, my code could be:

INSERT INTO TESTC 
(SELECT * FROM TESTC WHERE e02_code = '045934');
UPDATE TESTC SET e02_code = '045935';
COMMIT;

but I cannot do this because:
I do not want to change the already existing row, just take a 'copy' of it and change the e02_code.
I know how to do this with a cursor, but I wonder if there is a 'mass' way to do this, using SQL only.

Can anyone help?

 

[SantaMufasa]

Patrick,

Then do this:

CREATE TABLE DUMMY AS SELECT * FROM TESTC WHERE e02_code = '045934');
UPDATE DUMMY SET e02_code = '045935';
COMMIT;
INSERT INTO TESTC SELECT * FROM DUMMY;
DROP TABLE DUMMY;

Let us know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[patrickdrd]

Of course this could be the solution,
but (in my Visual Basic application) all users log on using the same username, so what about 2 users running this at the same time?

 

[SantaMufasa]

I'm sorry Patrick...I forgot about the multiple-simultaneous-users-of-the-same-login specification in your original post...Oh that's right...it's not there. <grin>

We can solve only the problems of which we are aware, bud. We didn't know until your most recent post about that problem. And we still don't know if this is a one-time update or you are doing this constantly. It's sounding more like this is an ongoing issue since you are concerned about multiple, simultaneous logins performing this same task.

Help us understand why multiple, concurrent users would all want to repetitively copy '045934' records to become duplicate '045935' records in the same table. Without full, upfront disclosure of your needs, this is turning into a game of "Carlotta" (...rules change as you play the game.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[patrickdrd]

Is it possible to do the same thing with a ref cursor or an index by table or a varray?

I just want to change a value before inserting into...

It must be done somehow!

 

[sem]

It may be done by listing all columns you need


Regards, Dima

http://seminihin.narod.ru

 

[patrickdrd]

no, I have too many columns and too many tables.
right now I'm writing it using temporary tables with dynamic sql, i.e. I store the rows I need in the temporary table, update the column I need and then get them and insert them back into the original table

 

[sem]

You may generate that statements by querying column list from data dictionary.

Regards, Dima

http://seminihin.narod.ru