Home » SQL & PL/SQL » SQL & PL/SQL » problem adding column values against multiple rows
problem adding column values against multiple rows [message #255710] Wed, 01 August 2007 09:49 Go to next message
ucuffdm
Messages: 4
Registered: August 2007
Junior Member
I have a table set up as follows:

data_domain
Name Null? Type
------------------------------- -------- ----
DATA_DOMAIN_ID NOT NULL NUMBER(9)
DATA_DOMAIN_NAME VARCHAR2(20)
DATA_DOMAIN_DESC VARCHAR2(40)
PARENT_DATA_DOMAIN_ID NUMBER(9)

I have several rows that contain DATA_DOMAIN_ID and PARENT_DATA_DOMAIN_ID values in the 2400000's range that I want to change to the 2700000's range. Since the data domain id and parent data domain id columns are both numbers, I'd like to just add 300000 to the values so that I can bypass all the constraints.

The issue is finding the right query to do this and if it can be done at all.

I tried the following query, but got constraint issues:

update data_domain set DATA_DOMAIN_ID=DATA_DOMAIN_ID+300000, PARENT_DATA_DOMAIN_ID=parent_data_domain_id+300000
where DATA_DOMAIN_ID >2399999 and DATA_DOMAIN_ID <2500000

I dont' know if this is b/c what I want to do can't be done or if I just don't have my query set up correctly.

Any help would be greatly appreciated!!!
Thanks!
Re: problem adding column values against multiple rows [message #255729 is a reply to message #255710] Wed, 01 August 2007 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried the following query, but got constraint issues

Which ones?
Copy and paste your screen.

In addition,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).


Regards
Michel
Re: problem adding column values against multiple rows [message #255742 is a reply to message #255729] Wed, 01 August 2007 11:20 Go to previous messageGo to next message
ucuffdm
Messages: 4
Registered: August 2007
Junior Member

Here is the query (I had included it in my original post):

update data_domain set DATA_DOMAIN_ID=DATA_DOMAIN_ID+300000, PARENT_DATA_DOMAIN_ID=parent_data_domain_id+300000
where DATA_DOMAIN_ID >2399999 and DATA_DOMAIN_ID <2500000

The were part isn't the problem. It's the trying to add 300000 to the original values.

Thanks!
Re: problem adding column values against multiple rows [message #255746 is a reply to message #255742] Wed, 01 August 2007 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you include the query in your first but didn't format it and still don't.
I asked for the error, you still don't show it.
I asked for a copy and paste of your screen you don't do it.

YOYO.

Regards
Michel
Re: problem adding column values against multiple rows [message #255752 is a reply to message #255746] Wed, 01 August 2007 11:58 Go to previous messageGo to next message
ucuffdm
Messages: 4
Registered: August 2007
Junior Member
Here is the cut and past from my screen:

  1  update data_domain set DATA_DOMAIN_ID=DATA_DOMAIN_ID+300000, PARENT_DATA_DOMAIN_ID=parent_data_domain_id+300000
  2* where DATA_DOMAIN_ID >2399999 and DATA_DOMAIN_ID <2500000

dwowner> /

And here is the error:

ERROR:
ORA-06550: line 1, column 29:
PL/SQL: Compilation unit analysis terminated


update data_domain set DATA_DOMAIN_ID=DATA_DOMAIN_ID+300000, PARENT_DATA_DOMAIN_ID=parent_data_domain_id+300000
*
ERROR at line 1:
ORA-00001: unique constraint (DWOWNER.PK_DATA_DOMAIN) violated


Thanks for your help!!
Re: problem adding column values against multiple rows [message #255756 is a reply to message #255752] Wed, 01 August 2007 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00001: unique constraint (DWOWNER.PK_DATA_DOMAIN) violated

Verify that any target id is not already taken.

Regards
Michel
Re: problem adding column values against multiple rows [message #255758 is a reply to message #255710] Wed, 01 August 2007 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>ERROR:
>ORA-06550: line 1, column 29:
>PL/SQL: Compilation unit analysis terminated

You apparently are not really showing us every thing you are doing.

You're On Your Own (YOYO)!

Re: problem adding column values against multiple rows [message #255762 is a reply to message #255756] Wed, 01 August 2007 12:25 Go to previous message
ucuffdm
Messages: 4
Registered: August 2007
Junior Member
That was it -- I had one DATA_DOMAIN_ID with the 2400000 value in it and it was causing the problem.

Thanks sooo much for your help!!
This has saved me tons of manual work Smile
Previous Topic: problem with my pivot table
Next Topic: select systimestamp + tz_offset('US/Central') from dual;
Goto Forum:
  


Current Time: Wed Dec 07 09:05:42 CST 2016

Total time taken to generate the page: 0.11531 seconds