"The multi-part identifier could not be bound" SQL Server UPDATE command with joins

Question

I have an issue with an UPDATE command where I get the error

The multi-part identifier "Day_settings.temp" could not be bound.

when trying to access a column value from a left join. When selecting, it works fine.

For example:

SELECT * 
FROM Animals 
LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id 
LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id

That works as expected. It also works when getting the value, like so:

SELECT Day_settings.temp, Night_settings.temp 
FROM Animals 
LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id 
LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id

The issue arrives when updating. I have tried fixing this by reading other stackoverflow questions, and none of the solutions work. Either using dbo.tablename or writing the selector after each JOIN statement has no effect.

The command is as follows:

 UPDATE Animals  
 SET Animals.title = @title,  
     Animals.is_active = @is_active,  
     Day_settings.temp = @day_settings_temp,  
     Day_settings.humid = @day_settings_humid,  
     Day_settings.light = @day_settings_light,  
     Day_settings.time = @day_settings_time,  
     Night_settings.temp = @night_settings_temp,  
     Night_settings.humid = @night_settings_humid,  
     Night_settings.light = @night_settings_light,  
     Night_settings.time = @night_settings_time  
 FROM Animals  
 LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id  
 LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id  
 WHERE Animals.id = @id

It seems to be following the order and syntax of all the examples I found online.

Any help would be very appreciated, I am truly lost.

Answer 1

You can only update one table at a time. You seem to want:

UPDATE a
    SET title = @title,  
        is_active = @is_active
    FROM Animals a
    WHERE a.id = @id;

UPDATE ds  
    SET temp = @day_settings_temp,  
        humid = @day_settings_humid,  
        light = @day_settings_light,  
        time = @day_settings_time
   FROM Animals a JOIN
        Day_settings ds
        ON a.fk_day_id = ds.id  
   WHERE Animals.id = @id;

UPDATE ns  
    SET temp = @night_settings_temp,  
        humid = @night_settings_humid,  
        light = @night_settings_light,  
        time = @night_settings_time  
    FROM Animals a JOIN 
         Night_settings ns
        ON a.fk_night_id = ns.id  
    WHERE a.id = @id;

Notes:

• You can wrap these in a transaction if you want them all to take effect "at the same time".
• Table aliases make the query easier to write and to read.
• I removed the aliases in the SET. They are allowed, but because only one table can be updated, I think that might be confusing.
• LEFT JOINs are not necessary. You need a matching row in the second table to do the update.

Answer 2

You can only update 1 table per statement (unless you having triggers enabled). The start of you UPDATE says so:

UPDATE Animals SET

If you are not updating the joining fields or fields that are used on filtering conditions (or updating expressions), you can repeat the same join to update each table on different UPDATE statements:

UPDATE Animals  
 SET  
 title = @title,  -- You can omit the table alias here, as it's already mentioned right after the "UPDATE"
 is_active = @is_active
 FROM Animals  
 LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id  
 LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id  
 WHERE Animals.id = @id


 UPDATE Day_settings  
 SET  
 temp = @day_settings_temp,  
 humid = @day_settings_humid,  
 light = @day_settings_light,  
 time = @day_settings_time
 FROM Animals  
 LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id  
 LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id  
 WHERE Animals.id = @id

 UPDATE Night_settings  
 SET  
 temp = @night_settings_temp,  
 humid = @night_settings_humid,  
 light = @night_settings_light,  
 time = @night_settings_time  
 FROM Animals  
 LEFT JOIN Day_settings ON Animals.fk_day_id = Day_settings.id  
 LEFT JOIN Night_settings ON Animals.fk_night_id = Night_settings.id  
 WHERE Animals.id = @id

Since this seems like a transactional operation, you might want to ensure all 3 updates are correctly done, or none of them. Wrap the updates with a TRANSACTION and make sure to either COMMIT if OK or ROLLBACK if not. This is a simple transaction handling example:

BEGIN TRY

    BEGIN TRANSACTION

        UPDATE Table1 SET ....

        UPDATE Table2 SET ....

        UPDATE Table3 SET ....

    COMMIT

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage VARCHAR(MAX) = ERROR_MESSAGE()

    IF @@TRANCOUNT > 0
        ROLLBACK

    RAISERROR(@ErrorMessage, 15, 1)

END CATCH

Answer 3

Are you trying to update the 3 tables in same time ?

I think you can only update tables 1 by 1

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eab6e48d-d22a-4e1f-add6-aaf3b47bdd84/how-to-update-multiple-tables-in-sql-server-2008-?forum=transactsql