Help with an update query...

[THoey]

I am trying to write a query on a Oracle 7.3.4 database to update the worktype column in one table with the worktype column in another table. The tables I am using are:

tbl_Agent_List
--------------
AGENTID VARCHAR2(4)
WORKTYPE VARCHAR2(3)
CENTER VARCHAR2(3)
...

This table has a record for each agent that contains employee information including their current worktype. The CENTER column is used to distinguish between like AGENTID's from different locations. I am only interested in getting the information from agents at location "SAN".

Agent
-----
DATETIME DATE
AGENTID VARCHAR2(4)
WORKTYPE VARCHAR2(3)
ACD_ID VARCHAR2(6)
...

This table has a daily record for each agent, for each queue(ACD), with what they did while they were logged in. Since the worktype could change from day to day, we want to be able to track what work they did, for which worktype, for each day.

For each record in tbl_Agent_List, there could be multiple records in Agent, showing work in multiple queues (ACD's).

I wrote a select query to pull the AGENT.AGENTID and it's corresponding tbl_AGENT_LIST.WORKTYPE and got that to work:

SELECT Agent.agent_id, tbl_Agent_List.worktype
FROM Agent, tbl_Agent_List
WHERE Agent.agent_id = tbl_Agent_List.agentid AND
tbl_Agent_List.center = 'SAN' AND
substr(Agent.datetime, 1, 10) LIKE SYSDATE - 1
ORDER BY 1

This query pulls the right number of records and has the correct worktype for each agent. But all of my attempts today to write an update query have caused it to say "multiple rows returned" or have invalid column names.

Any help on this would be greatly appreciated. Remember, Santa is watching for naughty or nice...


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?

 

[MikeJones]

I've often found that this sort of subquey update can be rewritten more simply and often just as effecient (In terms of Performence in PL/SQL. Have you tried it? So long as you use rowid's where ever possible and parameters in to your cursor so that its not getting reparsed all the time you should be OK.