Check if value exists in table to determine wether to use UPDATE or INSERT
586910Aug 22 2008 — edited Aug 22 2008Hi,
I try to create a block which determines the value of a calculated amount and compares it against 100. If the value is less than 100 then an INSERT or UPDATE should take place against the LAGER_TEST table. Though I have a hard time figuring out how you should test if the item does not exist in LAGER_TEST and then do an INSERT or if it exists do an UPDATE to the existing item in the LAGER_TEST table.
When I use the code below it doesn't detect that items indeed exist in the LAGER_TEST and hence try to do INSERTS all the time instead of UPDATE.
Please see code below, though, the code below is of secondary importance, what I am really after is a general solution for how to find out if a value exists or not (in a table) and from that decide if INSERT or UPDATE should take place. With only SQL of course some EXISTS statement would suit fine, but I haven't managed to construct something similar within PLSQL.
DECLARE
MELLANSKILLNAD NUMBER(3);
LAGER_VAR VARCHAR2(10);
TESTVAR VARCHAR2(10);
CURSOR LAGER_CUR IS
SELECT la.ARTNR
FROM LAGER_TEST la
WHERE la.ARTNR=TESTVAR;
BEGIN
OPEN LAGER_CUR;
FOR FP_VARIABEL IN
(
SELECT a.ARTNR, a.FP_STORLEK, a.FP_STORLEK*a.LISTPRIS*(1-m.INKOP_RABATT/100) AS dream FROM ARTIKEL_TEST a,
MTRLGRUPP m WHERE a.MTRLID=m.MTRLID
AND a.ARTNR IN ( 'E0100070', 'E0100075', 'E0100440')
)
LOOP
IF FP_VARIABEL.dream<100 THEN
TESTVAR:= FP_VARIABEL.ARTNR;
dbms_output.put_line(TESTVAR);
FETCH LAGER_CUR INTO LAGER_VAR;
IF LAGER_CUR%NOTFOUND THEN
INSERT INTO LAGER_TEST
VALUES(FP_VARIABEL.ARTNR,FP_VARIABEL.FP_STORLEK);
ELSE
UPDATE LAGER_TEST SET ANTAL = '5'
WHERE ARTNR =TESTVAR;
END IF;
MELLANSKILLNAD:=100-FP_VARIABEL.dream;
dbms_output.put_line(MELLANSKILLNAD);
ELSE
dbms_output.put_line('ANNAT');
END IF;
END LOOP;
END;
/
All tips welcome