AW: Record by record update with SQL with no matching key.

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Dennis,

Common Table Expressions are only allowed for SELECT-Statements, but do not
work in composition with Update!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
Gesendet: Monday, 01. February 2010 17:46
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Record by record update with SQL with no matching key.

> So, how would I join the two records by rownumber?  I think I have to
> use a subquery since I'll need to alias the rownumber results but I
> can't quite get my head around the syntax
>
> The update would look like:
> update realtable set firstname = firstfake, lastname = lastfake
>
> the where would use the derived rownumber columns (say rn1,and rn2)

There are some rules about what can follow a WITH statement, and I don't
have the facility to test this concept, but here's my stab:

WITH T1 AS (
    SELECT rownumber fakerow, firstname firstfake, lastname lastfake
    FROM FAKETALE    
    )
, T2 as (
    SELECT rownumber realrow, keyval realkey
    From REALTABLE
    )
UPDATE REALTABLE SET firsname = firstfake, lastname = lastfake
       Where keyval = realkey and fakerow = realrow

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Democracy is the worst system devised by the wit of man, except for all the
others."
        -- Winston Churchill