[da_coolestofall]

Is it possible to have a cursor pulling data from 2 different tables and marking it as FOR UPDATE and then using the UPDATE statement for one table with the WHERE CURRENT OF clause. For example can I do this

CURSOR C2 IS
SELECT t1.date_created, t2.customer, t2.name1, t2.sman
FROM lead_when t1, leads t2
where t1.leadno = t2.leadno AND
t1.lastsale is null
FOR UPDATE of t2.sman;
BEGIN
FOR c2_index in c2
LOOP
IF(c2_index.date_created < sysdate -180)
THEN
INSERT into sman_null_backup (sman, customer, name1) VALUES (c2_index.sman, c2_index.customer, c2_index.name1);
UPDATE leads
SET sman = NULL
WHERE CURRENT OF C2;
END IF;
END LOOP;
CLOSE C2;
COMMIT;

Will this update the LEADS table. Also, do I need to have commit there, or is it implicit?

[andrewst]

Yes, you can do that. You should explicitly commit.

Your code can be improved. First, the condition " IF(c2_index.date_created < sysdate -180)" should be part of the cursor definition. Second, there is no need to close the for-loop cursor explicitly (in fact, that will fail). So:

DECLARE
  CURSOR C2 IS
    SELECT t1.date_created, t2.customer, t2.name1, t2.sman
    FROM lead_when t1, leads t2
    WHERE t1.leadno = t2.leadno
    AND t1.lastsale is null
    AND t1.date_created < sysdate-180
    FOR UPDATE of t2.sman;
BEGIN
  FOR c2_index in c2
  LOOP
    INSERT into sman_null_backup (sman, customer, name1) VALUES (c2_index.sman, c2_index.customer, c2_index.name1);
    UPDATE leads
    SET sman = NULL
    WHERE CURRENT OF C2;
  END LOOP;
  COMMIT;
END;

[da_coolestofall]

Thanks! And about the condition being part of the cursor....I kinda feel silly for not doing that. Thank you so much for pointing that out.