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?

 

[carp]

Terry -
Try this:

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

 

[THoey]

Carp,

Thanks for the suggestion. I tried it, but it didn't work.

SQLWKS> UPDATE Agent A
     2> SET A.worktype = (
     3>    SELECT worktype
     4>    FROM tbl_Agent_List L
     5>    WHERE (L.center = 'SAN') AND 
     6>          (A.agent_id = L.agentid)) AND 
     7> (substr(A.datetime,1,10) = SYSDATE - 1) ; 
         (A.agent_id = L.agentid)) AND 
                                   *
ORA-00933: SQL command not properly ended

I moved the &quot" from before the AND and placed it at the end, like so:

SQLWKS> UPDATE Agent A
     2> SET A.worktype = (
     3>    SELECT worktype
     4>    FROM tbl_Agent_List L
     5>    WHERE (L.center = 'SAN') AND 
     6>          (A.agent_id = L.agentid) AND 
     7> (substr(A.datetime,1,10) = SYSDATE - 1)) ; 
316743 rows processed.
SQLWKS> rollback
Statement processed.

This updated every record in the table, not just the records I was trying to update.

In another forum, someone suggested the following:

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.agent_id IN (
   SELECT a.agent_id
   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))

But that failed due to returning multiple rows. Could that have anything to do with the IN clause?

This is turning out to be such a pain.

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]

Terry,

Does your Agent table have a primary key field? If so, try to replace agent_id with this primary key field in the following part of the sql: "...WHERE a.agent_id IN (SELECT a.agent_id...":

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.agent_primary_key IN (
SELECT a.agent_primary_key
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]

The following is a description of the tbl_Agent_List table:

SQLWKS> desc tbl_agent_list
Column Name                    Null?    Type
------------------------------ -------- ----
AGENTID                        NOT NULL VARCHAR2(4)
LASTNAME                       NOT NULL VARCHAR2(20)
FIRSTNAME                      NOT NULL VARCHAR2(20)
SUPERVISOR                              VARCHAR2(20)
SENORDER                                VARCHAR2(11)
WORKTYPE                       NOT NULL VARCHAR2(3)
CENTENIUMID                             VARCHAR2(7)
SUPER1ID                                VARCHAR2(4)
SUPER2ID                                VARCHAR2(4)
ADDED                          NOT NULL DATE
ADDEDBY                        NOT NULL VARCHAR2(10)
UPDATED                        NOT NULL DATE
UPDATEDBY                      NOT NULL VARCHAR2(10)
CENTER                         NOT NULL VARCHAR2(3)

A unique record can be found based on the AGENT_ID, and the CENTER fields.

The table tbl_Agent_List will have only one record in it per agent. Think of it as a personnel file.

The Agent table is described as so:

SQLWKS> desc agent
Column Name                    Null?    Type
------------------------------ -------- ----
DATETIME                       NOT NULL DATE
AGENT_ID                       NOT NULL VARCHAR2(4)
ACD_NAME                       NOT NULL VARCHAR2(8)
AGENT_NAME                     NOT NULL VARCHAR2(10)
CALLSANSWD                              NUMBER
TOTLOGTIME                              NUMBER
TOTHLDTIME                              NUMBER
TOTNOTRDY                               NUMBER
TOTACDTLK                               NUMBER
TOTWAIT                                 NUMBER
TOTDNTIME                               NUMBER
TOTDNCALLS                              NUMBER
ACDCLSXFER                              NUMBER
SHORTCALLS                              NUMBER
TOTCONSLT                               NUMBER
NTOFCALLS                               NUMBER
WORKTYPE                                VARCHAR2(3)

A unique record can be found based on the DATETIME, AGENT_ID, and ACD_NAME fields (possible clue).

The Agent table will have one record for every day, for every agent, for each ACD queue they are logged into.

An agent could be transferred from one worktype (think department) into another. They want the daily records to show the department that they were assigned to on that day. That is why I am updating the worktype field just for yesterdays records. Then, at the end of the month, a report can be pulled showing which department to bill (i.e. if little Johnnie worked 3 weeks for department A and 1 week for department B, then end of month report would show that three weeks worth of data as being charged to department A and the rest to department B).

Sorry if I over simplified that, this just seems like it should be so easy and it is not turning out that way.

I do appreciate the help that you and Carp have given so far...

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]

Since the agent table doesn't have a primary key, try the following:

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.agent_id, a.datetime, a.acd_name) IN (
SELECT a.agent_id, a.datetime, a.acd_name
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))

Hope it works.

 

[THoey]

I took your code and then had to change the "= SYSDATE" to "LIKE SYSDATE" because it wasn't making a match (0 rows returned). After I made that change, I got the "ORA-01427: single-row subquery returns more than one row
" response.

SQLWKS> UPDATE Agent a
     2> SET a.worktype = (
     3>    SELECT al.worktype
     4>    FROM tbl_Agent_List al
     5>    WHERE (al.center = 'SAN') AND 
     6>          (a.agent_id = al.agentid))
     7> WHERE (a.agent_id, a.datetime, a.acd_name) IN (
     8>    SELECT a.agent_id, a.datetime, a.acd_name
     9>    FROM agent a, tbl_Agent_List al
    10>    WHERE (al.center = 'SAN') AND
    11>          (SUBSTR(a.datetime,1,10) LIKE SYSDATE - 1) AND 
    12>          (a.agent_id = al.agentid))
    13> 
ORA-01427: single-row subquery returns more than one row

I am starting to wonder if I am going to have to add a PK field to the table structure.

Thanks for the assist...

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]

Near as I can tell you're getting this error message because the subquery
3> SELECT al.worktype
4> FROM tbl_Agent_List al
5> WHERE (al.center = 'SAN') AND
6> (a.agent_id = al.agentid)
is returning more than one record for an agent_id. Based on your description of the table tbl_Agent_List this shouldn't be so, but I would check to see if you have multiple records with the same agent_id where center='SAN'.

 

[MikeJones]

If you do a

SELECT al.worktype, count(*)
FROM tbl_Agent_List al
WHERE (al.center = 'SAN') AND
(a.agent_id = al.agentid)
Group by al.worktype
having count(*) > 1

This will give you any duplicates the subquery is returning...

 

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