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.

Fresh80@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

Fresh80@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