Update Query Help...

[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
[code]
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.   

The following is close, I think, but still isn't quite it:
[code]
UPDATE Agent
SET Agent.worktype = (
   SELECT DISTINCT tbl_Agent_List.worktype
   FROM 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)

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]

Terry,

I think you need to build in a restriction on your update stament as at the moment you will update every record in Agent. I'm not sure exactly what you need but how about...

UPDATE Agent
SET Agent.worktype = (
SELECT DISTINCT tbl_Agent_List.worktype
FROM 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)
WHERE EXISTS (SELECT 1
FROM tbl_agent_list t2
WHERE agent.agent_id = t2.agentid
AND t2.center = 'SAN')

 

[THoey]

I'll give it a try this morning. Thanks...

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]

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?

 

[connectxyzzy]

Make the subquery a correlated subquery:

UPDATE Agent TAB1
2> SET Agent.worktype = (
3> SELECT DISTINCT tbl_Agent_List.worktype
4> FROM tbl_Agent_List
-------> 5> WHERE TAB1.agent_id = tbl_Agent_List.agentid AND
6> tbl_Agent_List.center = 'SAN' AND
7> substr(Agent.datetime, 1, 10) like SYSDATE - 1)

 

[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?