Oracle Apex update specific row of table

Hi i want to update a specific row of my oracle database via apex. So i need to reach/address the specific row via where clause.

example entries:

GATTUNG	ART	UNTERART	ABART	VARIETÄT
AAA	AAA	AAA	NULL	NULL
AAA	AAA	NULL	NULL	AAA
AAA	AAA	NULL	NULL	NULL

Now i have two approaches.

first:

UPDATE TBL_PFLANZE
SET NAMEN =:P1_NAMEN
WHERE (GATTUNG = :P1_GATTUNG AND ART = :P1_ART_KREUZUNG)
AND ((UNTERART=:P1_UNTERART OR :P1_UNTERART IS NULL) AND (VARIETÄT=:P1_VARIETAET OR :P1_VARIETAET IS NULL) AND (ABART=:P1_ABART OR :P1_ABART IS NULL));

second:

UPDATE TBL_PFLANZE 
SET NAMEN ='&P1_NAMEN.' 
WHERE (GATTUNG = '&P1_GATTUNG.' AND ART = '&P1_ART_KREUZUNG.') 
AND (UNTERART &P1_WHERE_UNTERART. AND VARIETÄT &P1_WHERE_VARIETAET. AND ABART &P1_WHERE_ABART.);

the differences of both approaches are the P1_WHERE_...variables.

the first one use for example :P1_UNTERART and contains the whole value
the second one use for example &P1_WHERE_UNTERART. and contains = '&P1_UNTERART.' OR IS NULL

my problem is:

the first one updates all entries if i only set GATTUNG and ART (if i do not specify one of the other variables)
the second will work, but is not the right approach as I should be using binding variables instead of &VAR.

So my question is, how can i use the first approach which the desired result… 🙁

Answer

Does this do the trick ? It will update the rows if any of the columns have the same value as the corresponding page item or the database column and page item are both null:

UPDATE tbl_pflanze
   SET
  namen = :P1_NAMEN
 WHERE
  ( gattung = :P1_GATTUNG AND art = :P1_ART_KREUZUNG)
    AND
  ( ( NVL(unterart,'X') = NVL(:P1_UNTERART,'X')) AND
    ( NVL(varietät,'X') = NVL(:P1_VARIETAET,'X')) AND
    ( NVL(abart,'X') = NVL(:P1_ABART,'X')) );

Advertisement

Answer