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?

 

[Dmytro]

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

It works if subqury returns not more then 1 row per Agent row. If it's not the case you must specify some additional conditions for subquery e.g. dummy .. and rownum=1

 

[listener22]

Something else to try is trunc(sysdate) -1, since
sysdate has a fractional component. Jim

oracle, vb

 

[THoey]

Thanks for the responses.

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.).

As for the SYSDATE command, I will give that a try, but I use that same syntax heavily in almost everyone of my other "daily" queries and it works fine.

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?