Sql Server Update Column from another table


I have a table, where I need to update a data that is in another table according to a key, but it does not update the data correctly.

Update KM_ficha set FichaActual = rtrim(ltrim(B.Idhistorial))
from KM_ficha A, [dbo].[base datos cliente] B
where ltrim(rtrim(A.rut)) = ltrim(rtrim(B.rut))

However, the data is not updated in the column of table A. What am I doing wrong?

I miss a detail. Table B has repeated records, and I need to use the most current one based on Idhistorial

asked by Luis Gabriel Fabres 19.12.2018 в 14:09

2 answers


If you do it separately, as follows:

1- Get the most current record in a derived table.

2- And then do the update with the data of the derived table.

Update f set f.FichaActual = t.Idhistorial
from KM_ficha f
inner join (
    select ltrim(rtrim(A.rut)) as rut, max(rtrim(ltrim(B.Idhistorial))) as Idhistorial
    from KM_ficha A
    join [dbo].[base datos cliente] B
    on ltrim(rtrim(A.rut)) = ltrim(rtrim(B.rut))
    group by ltrim(rtrim(A.rut))
on ltrim(rtrim(f.rut)) = t.rut
answered by 19.12.2018 в 14:42

Try doing JOIN to update as follows:

UPDATE t1 SET t1.columna = (SELECT TOP 1 columna FROM tabla2 ORDER BY columna DESC)
FROM tabla1 t1 JOIN tabla2 t2 ON t1.columna = t2.columna

This way you make sure you are linking both tables and updating the columns you need.

You tell us:)

answered by 19.12.2018 в 14:29