“无法绑定多部分标识符”带有连接的SQL Server UPDATE命令

3 回答

• 1

  您一次只能更新一个表 . 你似乎想要：

  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;
  

  笔记：

  • 如果您希望它们全部生效，您可以将它们包装在事务中"at the same time" .

  • 表别名使查询更易于编写和读取 .

  • 我删除了 SET 中的别名 . 它们是允许的，但由于只能更新一个表，我认为这可能会令人困惑 .

  • LEFT JOIN 是没有必要的 . 您需要在第二个表中使用匹配的行来执行更新 .

  回复于 2024-05-08T04:59:36+08:00
• 1

  您是否尝试同时更新3个表？

  我认为你只能1比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

  回复于 2024-05-08T04:59:36+08:00
• 1

  每个语句只能更新1个表（除非您启用了触发器） . 你的开始 UPDATE 如此说：

  UPDATE Animals SET
  

  如果您没有更新用于过滤条件（或更新表达式）的连接字段或字段，则可以重复相同的连接以更新不同 UPDATE 语句上的每个表：

  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
  

  由于这似乎是一个事务操作，您可能希望确保正确完成所有3个更新，或者没有一个更新 . 使用 TRANSACTION 换行更新，如果没有，请确保 COMMIT ，否则确保 ROLLBACK . 这是一个简单的事务处理示例：

  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
  

  回复于 2024-05-08T04:59:36+08:00