-talk@houseoffusion.com
Sent: Thursday, January 20, 2005 6:28 PM
Subject: Re: Oracle help please
I was trying to run an update with a self-join. The table has no
primary key attributes. It's a an interface table for data migration.
Here is one variation:
UPDATE TABLE1
SET TABLE1.FIELD
TABLE1.KEY = TABLE2.KEY
AND TABLE1.FIELD = 'Y'
)
(You don't need the outer table in your inner query.)
- Original Message -
From: Frank Mamone [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Thursday, January 20, 2005 6:28 PM
Subject: Re: Oracle help please
I
Hi Jochem,
With everyone's help I finally did get the syntax working but I get the famous
'non-preserved key' message. I finally did it by updating the
majority doing a straight update and processing the exceptions
manually.
I was just wondering if you can enlighten me on the meaning of the
Frank Mamone wrote:
With everyone's help I finally did get the syntax working but I get the
famous
'non-preserved key' message. I finally did it by updating the
majority doing a straight update and processing the exceptions
manually.
I was just wondering if you can enlighten me on the
Frank,
What is the exact SQL that you are now trying to run?
Mario
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 4:42 PM
To: CF-Talk
Subject: Re: Oracle help please
Frank Mamone wrote:
With everyone's help I finally did get
-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 4:42 PM
To: CF-Talk
Subject: Re: Oracle help please
Frank Mamone wrote:
With everyone's help I finally did get the syntax working but I get the
famous
'non-preserved key' message. I finally did
Greg,
I tried this:
UPDATE EIM_CONTACT
SET A.CON_CUST_STAT_CD =
(
SELECT B.PC_STATUS
FROM EIM_CONTACT A , EIM_CONTACT B
WHERE A.ROW_ID = B.CON_PERSON_UID
AND B.CON_PR_POSTN = 'Y'
)
I get Table or View does not exist.
- Frank
On Mon, 17 Jan 2005 16:17:59 -0600, Greg Morphis [EMAIL
I worked out the syntax quirks and now it looks like this:
UPDATE eim_contact
SET con_cust_stat_cd =
(
SELECT
b.pc_status
FROM eim_contact a, eim_contact b
WHERE a.row_id = b.con_person_uid
AND b.con_pr_postn = 'Y'
)
Now I get : Single row sub-query returns more than one row.
I'm trying to
Just remove the From line, i.e.:
UPDATE TABLE1
SET TABLE1.FIELD = TABLE2.FIELD
WHERE TABLE1.KEY = TABLE2.KEY
AND TABLE1.FIELD = 'Y'
~|
Logware: a new and convenient web-based time tracking application. Start
tracking and
TABLE2 is actually the same table so I need to use an alias. Without a
from I cannot give it an alias name.
Thanks.
-Frank
On Tue, 18 Jan 2005 12:38:48 -0400, Rebecca Wells
[EMAIL PROTECTED] wrote:
Just remove the From line, i.e.:
UPDATE TABLE1
SET TABLE1.FIELD = TABLE2.FIELD
WHERE
Frank Mamone wrote:
TABLE2 is actually the same table so I need to use an alias. Without a
from I cannot give it an alias name.
If TABLE1 and TABLE2 are the same table, your example:
UPDATE TABLE1
SET TABLE1.FIELD = TABLE2.FIELD
FROM TABLE1, TABLE2
WHERE TABLE1.KEY =
I recommend reading Chapter 6 of Oracle Database 10g SQL or your
version equivalent.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2068235
-Adam
On Tue, 18 Jan 2005 19:18:01 +0100, Jochem van Dieten
[EMAIL PROTECTED] wrote:
Frank Mamone wrote:
Definately not what I want. This is an ETL job. So the system exports
the data from several tables and denormalizes it. Then I need to do
the updates and re-import.
So as you can imagine there will be a lot of empty rows.
Here is what I have:
ROW_ID
CON_PERSON_UID
PC_STATUS
CON_CUST_STATUS
I am surprised something like the example I emailed earlier would not
work for this. Though maybe I am skimming the problem too much. I
update our LDAP table off of itself via the merge statement. My guess
on 9i
MERGE INTO EIM_CONTACT A
USING ( SELECT B.PC_STATUS, B.CON_PERSON_UID
Frank Mamone wrote:
ROW_ID
CON_PERSON_UID
PC_STATUS
CON_CUST_STATUS
CON_PR_POSTN
I need to put the value of PC_STATUS into CON_CUST_STATUS where
CON_PERSON_UID matches ROW_ID and marked as CON_PR_POSTN = 'Y'
Remember this is denormalized data. So, essentially I need to do a
self
You are right about that. I tried it on MS with another field giving a
unique complex key.
The denormalized tables are confusing the hell out of me and I may be
making the a big assumption about what the 'Y' flag really means.
Since, the system puts the Y there, that is, there is no
I am having trouble doing an update with a join in Oracle. I am
getting this error:
SQL command not properly ended.
Here is the generic syntax I am using:
UPDATE TABLE1
SET TABLE1.FIELD = TABLE2.FIELD
FROM TABLE1, TABLE2
WHERE TABLE1.KEY = TABLE2.KEY
AND TABLE1.FIELD = 'Y'
Thanks for your
Frank,
use something like this
UPDATE TEST1
SET CHR =
(
SELECT CHR FROM TEST2
WHERE TEST1.num = TEST2.num
)
adjust your query to...
UPDATE TABLE1
SET TABLE1.FIELD =
(
SELECT
TABLE2.FIELD
FROM TABLE1, TABLE2
WHERE TABLE1.KEY = TABLE2.KEY
AND TABLE1.FIELD = 'Y'
)
On Mon, 17 Jan 2005
Could do something like this, edit to match your tables/columns of course:
merge into tablea a
using tableb b
on (a.myvalue = b.myvalue)
when matched then update
set a.othervalue = b.othervalue
Or if not on 10G and on 9i:
merge into tablea a
using (
select distinct b.myvalue, b.othervalue
19 matches
Mail list logo
