maravilloso
6/12/2013 - 6:51 AM

Actualizar valores desde otras tablas

Actualizar valores desde otras tablas

UPDATE tmcenmuf
SET (clcenmuf, clofidel, clpais, clcomaut, clprovin, clmunici, nocenmuf, cltipent,
     dedircen, clcodpos, notelefo, nofax, demailcen, nonomdir, noapedir, demaildir) = ((
 
    SELECT  clcentro,
            CASE
                WHEN clcentro[3,4]='00' THEN clcentro[1,2]
                WHEN clcentro[1,2]='28' THEN (clcentro[3,4]+60)::INT::VARCHAR(5)
                ELSE clcentro[2,4]
            END AS clofidel, '11' AS clpais,
            (SELECT clcomaut FROM tmprovin WHERE clprovin=clcentro[1,2]) AS clcomaut,
            clcentro[1,2] AS clprovin, clmunici, nocentro AS nocenmuf, '1' AS cltipent,
            dedirec AS dedircen, clcodpos, rhcentro.notelefo, rhcentro.nofax, rhcentro.demail AS demailcen,
            nonombre AS nonomdir, noapell1||' '||noapell2 AS noapedir, rhpersona.demail AS demaildir
    FROM
        rhcentro, OUTER rhpersona
    WHERE
        rhcentro.idperdir=rhpersona.idperson AND tmcenmuf.clcenmuf=rhcentro.clcentro
 
))
WHERE EXISTS (
    SELECT * FROM rhcentro WHERE tmcenmuf.clcenmuf=rhcentro.clcentro
)
UPDATE rhusuario
SET * = ((
    SELECT * FROM sica@ids_inst7:rhusuario
    WHERE idusuari=rhusuario.idusuari
))
WHERE EXISTS (
        SELECT * FROM sica@ids_inst7:rhusuario
        WHERE idusuari=rhusuario.idusuari
    )