Call a View over SQL (DB2 UDB V8.1.6)

[Fresh80]

Hi Newsgroup.

I need to call a view over an SQL statement.
The SQL statement makes an update at a table and needs the view in the
where part of the statement.
Example:

UPDATE EK_USER.ARTIKELGRUNDDATEN_TEST ARTIKELGRUNDDATEN_TEST
SET NEU = '1'
where ARTIKELGRUNDDATEN_TEST.ARTNR = VIEW_ARTIKELGRUNDDATEN.ARTNR
and
ARTIKELGRUNDDATEN_TEST.HARTNR = VIEW_ARTIKELGRUNDDATEN.HARTNR

Can anybody help me?

Thanks,

Sascha.

[Knut Stolze]

Fr*****@gmx.de wrote:

Hi Newsgroup.

I need to call a view over an SQL statement.
The SQL statement makes an update at a table and needs the view in the
where part of the statement.
Example:

UPDATE EK_USER.ARTIKELGRUNDDATEN_TEST ARTIKELGRUNDDATEN_TEST
SET NEU = '1'
where ARTIKELGRUNDDATEN_TEST.ARTNR = VIEW_ARTIKELGRUNDDATEN.ARTNR
and
ARTIKELGRUNDDATEN_TEST.HARTNR = VIEW_ARTIKELGRUNDDATEN.HARTNR

Just query the view in a sub-select inside the WHERE clause.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Serge Rielau]

Fr*****@gmx.de wrote:

Hi Newsgroup.

I need to call a view over an SQL statement.
The SQL statement makes an update at a table and needs the view in the
where part of the statement.
Example:

UPDATE EK_USER.ARTIKELGRUNDDATEN_TEST ARTIKELGRUNDDATEN_TEST
SET NEU = '1'
where ARTIKELGRUNDDATEN_TEST.ARTNR = VIEW_ARTIKELGRUNDDATEN.ARTNR
and
ARTIKELGRUNDDATEN_TEST.HARTNR = VIEW_ARTIKELGRUNDDATEN.HARTNR

I'm not clear what this has to do with teh fact that you refer to a
view... anyway:
UPDATE T SET T.c1 = 1 WHERE EXISTS(SELECT 1 FROM S WHERE T.pk = S.pk)

If you need S in the SET clause as well I recommend the MERGE statement:

MERGE INTO T USING S ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET T.c1 = S.c1

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab