problem with 2 table pl/sql update script

[frasernm]

Hi,

I have a problem with a simple(!) pl/sql script that I'm using to update a small table of local employees (past & present) from another table of all current employees. The cursor query returns 160+ ids, the script then seems to loop ok, but no changes are being made - I have had it printing out the update statements which will each update 1 record when pasted at the sql*plus prompt, and I have included this line below the main script, as well as excerts from the describe table commands.

Thanks in advance,

Fraser

DECLARE

v_agentid agent.agentid%TYPE;

-- get ids of all those with no email in our table
CURSOR cur_agents_no_email IS
SELECT TRIM(agentid) AS agentid
FROM agent
WHERE email IS NULL;
BEGIN

OPEN cur_agents_no_email;

LOOP

FETCH cur_agents_no_email INTO v_agentid;

EXIT WHEN cur_agents_no_email%NOTFOUND;

-- update our table with emails from main table
UPDATE agent
SET email = (SELECT email FROM employee_t WHERE employee_id = 'v_agentid')
WHERE agentid = 'v_agentid';

END LOOP;

CLOSE cur_agents_no_email;

COMMIT;
END;
/

-- output query
DBMS_OUTPUT.PUT_LINE('UPDATE agent
SET email = (SELECT email FROM employee_t WHERE employee_id = ''' || v_agentid || ''')
WHERE agentid = ''' || v_agentid || ''';');


SQL> describe agent;
AGENTID NOT NULL VARCHAR2(20)
FORENAMES VARCHAR2(25)
SURNAME VARCHAR2(25)
JOBTITLE VARCHAR2(100)
EMAIL VARCHAR2(100)
SENIOR CHAR(3)
REPORT CHAR(3)

SQL> describe employee_t;

EMPLOYEE_ID NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(50)

 

[firefox]

Hi,

your updatestatement reads: where ID1 ='ID2'.
your ID is a varchar2 field. So your searching for the String 'ID2' not for the value of the variable ID2.

 

[frasernm]

Excellent, thanks for the help.

f.