Forum Stats

  • 3,838,876 Users
  • 2,262,407 Discussions
  • 7,900,778 Comments

Discussions

UPDATE CON INNER JOIN Y WHERE

User_E2Y4B
User_E2Y4B Member Posts: 1 Green Ribbon

Hola, buendas tardes, necesito hacer un update de una tabla, per que cumpla con uos requisitos, tengo el siguiente SELECT:

SELECT GR1457DMOVLAV.A_NUMNIT AS CEDULA,

 GR1457DMOVLAV.N_APELL1   AS APELLIDO1,

 GR1457DMOVLAV.N_APELL2   AS APELLIDO2,

 GR1457DMOVLAV.N_NOMBR1   AS NOMBRE1,

 GR1457DMOVLAV.N_NOMBR2   AS NOMBRE2,

 GR1457DMOVLAV.K_TIPPAG   AS TIPO_PAGO,

 GR1457DMOVLAV.K_PERANO   AS PERIODO,

 GR1457DMOVLAV.O_CUENTA   AS CUENTA,

 AH135D2021.K_NUMDOC     AS NUM_DOC,

 AH135D2021.K_TIPDOC     AS TIPODOC,

 AH135D2021.F_MOVIMI     AS FECHA_MOVIMIENTO_CUENTA,

 AH135D2021.V_CRPESO     AS CREDITO,

 AH135D2021.V_DBPESO     AS DEBITO,

 AH135D2021.N_MOVIMI     AS DESCRIPCION,

 GR1457DMOVLAV.K_SERIE    AS SERIE,

 GR1457DMOVLAV.K_FILA    AS FILA,

 GR1457DMOVLAV.F_MOVIMI   AS FECHA_MOVIMIENTO_LAVADO

FROM GR1457DMOVLAV

INNER JOIN AH136MCUENTA

ON GR1457DMOVLAV.O_CUENTA = AH136MCUENTA.A_NUMCTA

INNER JOIN AH135D2021

ON AH136MCUENTA.K_CUENTA   = AH135D2021.K_CUENTA

WHERE GR1457DMOVLAV.K_PERANO = '202109' AND AH135D2021.K_PERANO = '202109' AND GR1457DMOVLAV.K_TIPPAG = 2 AND AH135D2021.K_TIPDOC='CE'

ORDER BY GR1457DMOVLAV.A_NUMNIT;


EL UPDATE ES EL SIGUIENTE:


UPDATE

(SELECT GR1457.K_TIPPAG,GR1457.K_PERANO,GR1457.O_CUENTA, AH135D.K_TIPDOC 

 FROM GR1457DMOVLAV GR1457 

INNER JOIN AH136MCUENTA AH136

ON GR1457.O_CUENTA = AH136.A_NUMCTA

INNER JOIN AH135D2021 AH135D

ON AH136.K_CUENTA   = AH135D.K_CUENTA)

SET GR1457.K_TIPPAG='1' 

WHERE GR1457.K_PERANO = '202109' AND AH135D.K_PERANO = '202109' AND GR1457.K_TIPPAG = '2' AND AH135D.K_TIPDOC = 'CE';


Al ejecutar el update obtengo el siguiente error:

Error en la línea de comandos : 9 Columna : 95

Informe de error -

Error SQL: ORA-00904: "AH135D"."K_TIPDOC": identificador no válido

00904. 00000 - "%s: invalid identifier"


Que tengo mal?? Alguna ayuda? Gracias!!

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond

    Hi, @User_E2Y4B

    This question was posted in the English-language space. There's a separate space for Spanish language:

    Base de Datos Discusión General — oracle-tech

    I'll try to move the thread there for you.

  • mathguy
    mathguy Member Posts: 10,594 Blue Diamond

    Please note that this forum es solo en ingles. Either you state your question and accept answers in English, or look for a Spanish language forum (knowing that participation will be much more limited).

    As to the error: Tables like AH135D are understood within the subquery (the join), but not outside it. The UPDATE statement only sees the result of the subquery, with its columns.

    Either move the WHERE clause INSIDE the query, or give the subquery and its columns aliases and use those in the WHERE clause. (You may leave out the subquery alias - and perhaps even the column aliases, if there is no risk of confusion - but in a problem like this it is a best practice to alias the subquery and also the columns within it.)