How to do an UPDATE correlated with conditions in PL SQL?

Question

How to do an UPDATE correlated with conditions in PL SQL?

Navigation

#1 by (1 votes)

1

I have 2 tables:

Table Solicitudes , with columns Id_cliente and No_cuenta_prestamo
Table Disposiciones , with columns Id_cliente and Estado_Actual

Subsequently I run the following code:

ALTER TABLE solicitudes.
ADD respuesta varchar2(40) DEFAULT 'NO ES MODIFICACION'

And I add a new column to the table Solicitudes called Repuesta with a default value of text that says "NO ES MODIFICACION"

The value of the% Respuesta varies depending on whether the same Id_Cliente has a No_Cuenta_Prestamo or not. Apart from having to have a No_Cuenta_Prestamo , a record must also have a Estado_Actual equal to 1,2,10 or 12.

If you meet all these conditions, the record in the Respuesta column of the Solicitudes table should be changed to MODIFICACION .

This is the code that I use so far, based on a correlated update:

ALTER TABLE solicitudes.
   ADD RESPUESTA varchar2(40) DEFAULT 'NO ES MODIFICACION'


UPDATE Solicitudes
SET r.respuesta = 'MODIFICACION'
WHERE d.No_Cuenta_Prestamo = NOT NULL
AND r.Estado_Actual = '1' OR '2' OR '10' OR '12'
FROM Solicitudes r, Disposiciones d
;

Obviously PL SQL does not understand what happens, because after the UPDATE the expected expedition fails.

I want to look for an alternative to this method, a left join? A special selection? Any suggestions?

sql oracle plsql

asked by Sobyro 17.04.2018 в 21:27

source

1 answer

How to count names of dynamic registers in MySQL? 403 Forbidden when I try to consume using Spring RestTemplate

score 1 · Accepted Answer

what you want to do could do it with a PL in which you open a cursor and go doing the necessary checks or with a subselect. The fastest is a subselect. It would be something like this:

UPDATE Solicitudes
SET respuesta = 'MODIFICACION'
WHERE Estado_Actual IN ('1','2','10','12')
  AND  Id_cliente IN (SELECT Id_cliente
                        FROM Disposiciones 
                       WHERE No_Cuenta_Prestamo IS NOT NULL);