I am currently starting to work with Databases in Oracle and it is common to have to perform UPDATE from one table to another. Since the databases I have access to do not have many records, if I run an Update with inner join I can not evaluate the performance of the Update in time. But there is a DB that I understand has tables with 50,000 records or more.
Then I want to be prepared so that if given the case I must modify in the mentioned tables to execute an instruction correctly and not one that blocks the db.
The update that I use regularly is like this:
UPDATE (
SELECT Tabla1.Valor AS ValorAnterior
,Tabla2.Codigo AS ValorNuevo
FROM Tabla1
INNER JOIN Tabla2 ON Tabla1.Valor = Tabla2.Descripcion
WHERE Tabla1.Tipo = 1
) t
SET t.ValorAnterior = t.ValorNuevo;
Through the network I have seen the following instructions:
1) UPDATE Tabla1 SET Tabla1.Valor = (SELECT Tabla2.Codigo
FROM Tabla2
WHERE Tabla1.Valor = Tabla2.Descripcion)
WHERE Tabla1.Tipo=1
AND EXISTS (SELECT Tabla2.Codigo
FROM Tabla2
WHERE Tabla1.Valor = Tabla2.Descripcion);
2) MERGE INTO Tabla1 trg
USING (
SELECT t1.RowId AS rid
,t2.Codigo
FROM Tabla1 t1
JOIN Tabla2 t2 ON Tabla1.Valor = Tabla2.Descripcion
WHERE Tabla1.Tipo = 1
) src
ON (trg.RowId = src.rid)
WHEN MATCHED
THEN
UPDATE
SET trg.Valor = Codigo;
3) UPDATE (
SELECT t1.Valor
,t2.Codigo
FROM Tabla1 t1
INNER JOIN Tabla2 t2 ON t1.Valor = t2.Descripcion
WHERE t1.Tipo = 1
)
SET t1.Valor = t2.Codigo;
If someone can tell me, the most efficient instruction, very grateful.