Home » RDBMS Server » Server Administration » update remote database  () 1 Vote
update remote database [message #435852] Fri, 18 December 2009 07:21 Go to next message
anat1983
Messages: 6
Registered: December 2009
Junior Member
hello!
i am trying to update my table on remote database (sql server) and i have error:

UPDATE billing.pack_sub@billing SET sub_id = (select sub_id from subscriber)
*
ERROR at line 1:
ORA-00904: "SUB_ID": invalid identifier

i have this table:
SQL> desc billing.pack_sub@billing;
Name Null? Type
--------
sub_id NOT NULL NUMBER(10)
package_cost NUMBER(19,4)
phone_cost NUMBER(19,4)
sms_cost NUMBER(19,4)
multi_cost NUMBER(19,4)
is someone know what is the problem?
ty
Re: update remote database [message #435876 is a reply to message #435852] Fri, 18 December 2009 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

post DDL for SUBSCRIBER
Re: update remote database [message #435885 is a reply to message #435852] Fri, 18 December 2009 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>i am trying to update my table on remote database (sql server) and i have error:
>UPDATE billing.pack_sub@billing SET sub_id = (select sub_id from subscriber)

Upon further review, I suspect the problem involves the case of the column name.

UPDATE billing.pack_sub@billing SET "sub_id" = (select sub_id from subscriber)

above SQL might work better, then again it might not.
Re: update remote database [message #435896 is a reply to message #435885] Fri, 18 December 2009 12:11 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Moreover, if the "subscriber" table contains more than a single record, you'll get TOO-MANY-ROWS error.
Re: update remote database [message #435899 is a reply to message #435896] Fri, 18 December 2009 13:09 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Plus, the query would set EVERY sub_id in pack_sub to the same ID.
Re: update remote database [message #435900 is a reply to message #435899] Fri, 18 December 2009 13:11 Go to previous message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
ThomasG wrote on Fri, 18 December 2009 11:09
Plus, the query would set EVERY sub_id in pack_sub to the same ID.



This is why ROLLBACK exists. Wink
Need to debug one bug at a time.
Previous Topic: Upgrade from Oracle 9i to Oracle 10g
Next Topic: how can I get the redo log of one session
Goto Forum:
  


Current Time: Sun Sep 25 09:30:15 CDT 2016

Total time taken to generate the page: 0.06440 seconds