Home / Questions / 427078
Accepted
Hackerman
Asked: 2022-02-10 12:37:35 +0800 CST 2022-02-10 12:37:35 +0800 CST

Update column with data from another database

  • 772

I have the following update where I say that the weight column is updated with the data from the weight column but from the other database and where I tell it to be equal to the batch of the other database and the animal

UPDATE [2_Datos].dbo.tblmvto_lotes 
SET peso = (SELECT peso FROM [1_Datos].dbo.tblmvto_lotes) 
WHERE  codlote = (SELECT codlote FROM [1_Datos].dbo.tblmvto_lotes) AND 
animal =  (SELECT animal FROM [1_Datos].dbo.tblmvto_lotes)

But I get the following message
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

This is because it could be that there are more rows in one than the other, but if I am already making the comparison that the codlote and the animal are the same and the two databases are the same, I only need to update the weight field one by the other, as you can see in the img the data is different is to update it

enter image description here

enter image description here

sql

2 Answers

  1. Best Answer

    The problem is that your three subqueries are independent of each other and not related to the UPDATE. If you are indeed using SQL Server, you can use FROMand JOINto relate the tables and update with the corresponding values.

    UPDATE ml2
SET peso = ml1.peso
FROM [2_Datos].dbo.tblmvto_lotes AS ml2
JOIN [1_Datos].dbo.tblmvto_lotes AS ml1 ON ml2.codlote = ml1.codlote
                                       AND ml2.animal  = ml1.animal;
    • 3

  2. I would do it by performing a JOIN between the tables of both databases, validating the conditions mentioned.

    UPDATE A
    SET A.peso = B.peso
FROM [2_Datos].dbo.tblmvto_lotes AS A
    INNER JOIN [1_Datos].dbo.tblmvto_lotes AS B
                                            ON  A.codlote   = B.codlote AND
                                                A.animal    = B.animal
    • 2