CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Ted Mosby
Ted Mosby

Reputation: 89

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.

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

my problem is:

  1. the first one updates all entries if i only set GATTUNG and ART (if i do not specify one of the other variables)
  2. 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... :(

Upvotes: 0

Views: 2806

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18665

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')) );

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142733

The way I see it, the 1st query is correct while the 2nd is wrong.

Why is it wrong? Because you're using strings. This:

SET NAMEN ='&P1_NAMEN.' 
WHERE (GATTUNG = '&P1_GATTUNG.'

would want to put litally &P1_NAMEN. string into the NAMEN column for all rows whose GATTUNG column contains literally &P1_GATTUNG. string in it (and there's most probably none, so the 2nd query won't do anything, ever).

So, what is your problem, exactly? Why do you think that the 1st query doesn't work? Did you run the page in debug mode and reviewed debug results? Could it be that P1 page's items aren't stored into session state so UPDATE statement doesn't see them?

Also, where do you execute that UPDATE? As a process which runs after button is pressed? Something else?

Upvotes: 0

Related Questions