SEO

#1 (**permalink**) 02-27-2008, 04:11 AM

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.

#2 (**permalink**) 02-27-2008, 04:11 AM

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

#3 (**permalink**) 02-27-2008, 04:11 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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