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?

 

[listener22]

Terry
cc: Santa

Check whether you have any null Agent.agent_id's or null
tbl_Agent_List.agentid's.

If not, post your update stmt.

Jim

oracle, vb

 

[THoey]

Here are two versions of the query that I was playing with earlier. The first one was an obvious error, and the second one worked too good (It should only update 1100 records)

SQLWKS> UPDATE Agent
     2> SET Agent.worktype = (
     3>    SELECT tbl_Agent_List.worktype
     4>    FROM tbl_Agent_List
     5>    WHERE Agent.agent_id = tbl_Agent_List.agentid AND
     6>          tbl_Agent_List. center = 'SAN' AND
     7>          substr(Agent.datetime, 1, 10) like SYSDATE - 1)
ORA-01427: single-row subquery returns more than one row
SQLWKS> UPDATE Agent
     2> SET Agent.worktype = (
     3>    SELECT DISTINCT tbl_Agent_List.worktype
     4>    FROM tbl_Agent_List
     5>    WHERE Agent.agent_id = tbl_Agent_List.agentid AND
     6>          tbl_Agent_List.center = 'SAN' AND
     7>          substr(Agent.datetime, 1, 10) like SYSDATE - 1)
311980 rows processed.
SQLWKS> rollback
Statement processed.

Thanks for taking a look. I know I am close with it, but I think I killed off too many brain cells this holiday season...


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?

 

[listener22]

Because your where clause applies to the selection
from tbl_Agent_list, and not to the update itself, there
should be about 312000 rows in Agent.

You'll probably want to qualify the update by setting
a where statement for it as well, after the right parens
having to do with the select. Jim

oracle, vb