Home » SQL & PL/SQL » SQL & PL/SQL » Pls help with SQL
Pls help with SQL [message #388744] Thu, 26 February 2009 01:17 Go to next message
Maverick27
Messages: 84
Registered: October 2008
Member
I'm trying to update a MASTER table with info from a TEMP table.


SELECT count(*)
FROM mst_item
WHERE EXISTS (SELECT *
FROM temp_prdtype
WHERE mst_item.vc_item_code = temp_prdtype. vc_item_code)
AND vc_comp_code = '01'


I used 'EXISTS' statement to verify NO. of records that exist in MASTER table & result returns 159 records.

However, when i use CURSOR LOOP to update the Master table, the code only updates 2 records.

CURSOR c2 IS
SELECT * FROM temp_prdtype;
BEGIN
FOR j IN c2
LOOP
UPDATE mst_item
SET vc_field3 = j.vc_colour,
WHERE vc_item_code = j.vc_item_code
AND vc_comp_code = '01' ;
END LOOP;
EXCEPTION
WHEN others THEN
null;
END;


Can somebody pls. tell me what the problem is ?

Thanks - Mave
Re: Pls help with SQL [message #388749 is a reply to message #388744] Thu, 26 February 2009 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First of all REMOVE WHEN OTHERS clause, then copy and paste what you did to prove what you say.
There we can start to talk.

Regards
Michel
Re: Pls help with SQL [message #388862 is a reply to message #388749] Thu, 26 February 2009 07:32 Go to previous message
Maverick27
Messages: 84
Registered: October 2008
Member
Michel - u are absolutely correct !!

FK INTEGRITY CONSTRAINT VIOLATION

I will need to review system design.

Human tendency is to always look for easy way out or look for someelse to blame, understandbly...

Mave

[Updated on: Thu, 26 February 2009 08:32]

Report message to a moderator

Previous Topic: How views works?
Next Topic: Hierargical query
Goto Forum:
  


Current Time: Thu Dec 08 22:19:06 CST 2016

Total time taken to generate the page: 0.09985 seconds