Append Query

[THoey]

First sorry for the cross post, but this is something that both Access Gods and/or Oracle Gods might be able to help me with a solution.

I am trying to create an UPDATE query in Oracle 7.3.4 to update a field in one table with the value from a field in another table. I can write the query in MS Access 97 and have gotten it to work. But when I try to copy it to Oracle's SQL Worksheet, make translations from Access to Oracle, it doesn't work.

The Access code that works is (for readability, I have removed the square brackets from around field names):

UPDATE EISADMIN_AGENT, EISADMIN_TBL_AGENT_LIST 
SET EISADMIN_AGENT.WORKTYPE = eisadmin_tbl_agent_list.worktype
WHERE (((EISADMIN_TBL_AGENT_LIST.CENTER)="SAN") AND
((EISADMIN_AGENT.DATETIME)=DateAdd("d",-1,Date())) AND
((EISADMIN_AGENT.AGENT_ID)=eisadmin_tbl_agent_list.agentid));

What I converted it to for Oracle and does not work is:

UPDATE Agent, tbl_Agent_List
SET Agent.worktype = tbl_Agent_List.worktype
WHERE ((tbl_Agent_List.center = 'SAN') AND 
(substr(Agent.datetime,1,10) = SYSDATE - 1) AND 
(Agent.agent_id = tbl_Agent_List.agentid));

The obvious error is on the first line having the ", tbl_Agent_List" before the set command. If I try removing it, I get invalid column names the first time I try to reference a field from that table.

I need to have this query in place by the first of the year and I am beating my head senseless against the wall. Any help would be greatly appreciated.


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[JeremyM]

I dont know Oracle too well. But in access you usually dont list the database, just the table. Have you tried taking out the database definition but still leaving the table?

Isnt the database usually set at start up so you would not need to re-specify?

Just a thought.(Maybe it'll work or spark an idea)

Jeremy

 

[THoey]

Thanks for the response.

I am not sure I understand the first comment, but the database is listed in the Access version of the query because those are ODBC linked tables. Access defaulted their name to that. In the Oracle query, I don't have to say which database I am using, and I list the table name out of habit, even where the field names are unique.

One other thing I forgot to mention, is that one side of the query does return multiple rows. The way these tables are setup, the Agent table is going to have multiple records in it for each agent_id. The Agent_List table will have only one record per agentid. Think of it this way, the Agent_List table is a personnel record table (id, name, dept, etc.) and the Agent table as a daily transaction table (id, project, time, etc.).

Any other suggestions would be greatly appreciated. Even if it doesn't work, it might point me in the right direction...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[albywalt]

Try this. Substitute the Agent table's primary key field for "pk_field".

UPDATE Agent a
SET a.worktype = (select al.worktype
from tbl_Agent_List al
where (al.center = 'SAN') and
(a.agent_id = al.agentid)
)
where a.pk_field In (select a.pk_field
from agent a, tbl_Agent_List al
where (al.center = 'SAN') and
(substr(a.datetime,1,10) = SYSDATE - 1) and
(a.agent_id = al.agentid)
)

 

[THoey]

Thanks, I'll give it a try...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[THoey]

Actually, we finally got it to work Friday. I knew it was easier than I was making it. But, I didn't trust Oracle's error messages (?) when I finally should have. The "returning multiple rows" was the problem. There were duplicate records. For those that helped, the final code is:

UPDATE Agent
SET Agent.worktype = (
SELECT DISTINCT tbl_Agent_List.worktype
FROM Agent, tbl_Agent_List
WHERE Agent.Agent_ID = tbl_Agent_List.Agentid AND
tbl_Agent_List.center = 'SAN'
WHERE (SUBSTR(Agent.datetime,1,10) LIKE SYSDATE - 1)

Thanks for all your help...



Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?