SQl查询使用字段的非null的where条件更新另一个表中的表

本文介绍了SQl查询使用字段的非null的where条件更新另一个表中的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我创建了一个触发器，用于将字段的记录从一个表复制到另一个表。我有两个表，即School和SignOff，其中School有一个主键SchoolID，signOff表有一个外键，用于学校的SchoolID字段。我们在SignOff表中为同一个SchoolID获得了多条记录。我想将SignSff表中的字段SchoolSignedOffDate复制到school表中的DateTravelPlanSubmitted。 我想只在表SignOff中的Submitted字段不为空时复制字段。直到现在我已经查询了 USE [TfLStarsDev] GO / * *** **对象：触发器[dbo]。[Trigger1]脚本日期：14/01/2016 09:50:10 ****** / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo]。[Trigger1] ON [dbo]。[SignOff] AFTER 更新 AS BEGIN TRY BEGIN TRANSACTION 更新学校 SET School.DateTravelPlanSubmitted = SchoolSignDate.SignoffSchoolDateMax FROM 学校 INNER JOIN SignOff ON School.SchoolID = SignOff.SchoolID 内部 加入（选择 SignOff .SchoolID，SignoffSchoolDateMax = max（SignOff.SignedOffBySchoolDate） 来自 SignOff 内部 加入学校 School.SchoolID = SignOff.SchoolID group by SignOff.SchoolID） SchoolS SchoolSignDate.SchoolID = School.SchoolID 其中 SignOff.Submitted IS NOT NULL COMMIT TRAN 结束 TRY BEGIN CATCH IF @@ TRANCOUNT > 0 ROLLBACK TRAN END CATCH 我在没有where条件的情况下复制。现在问题只在于where条件。如果我给出NOT NULL它显示错误。 任何人都可以帮忙解决这个问题。解决方案 假设你的触发器的其余部分工作正常，问题似乎是在where条件。 其中 SignOff.Submitted！= NOT NULL 正确的做法是 其中 SignOff.Submitted NOT NULL 更改条件并重试 I have created a trigger to copy the records of a field from one table to another. I have got two tables namely School and SignOff where School has a primary key SchoolID and the signOff table has a foreign key for the field SchoolID from school. We have got multiple records in the SignOff table for the same SchoolID . I want to copy the field SchoolSignedOffDate from SignOff table to DateTravelPlanSubmitted in school table.I want to copy field only if the Submitted field in the table SignOff is not null. Till now I have queried asUSE [TfLStarsDev]GO/****** Object: Trigger [dbo].[Trigger1] Script Date: 14/01/2016 09:50:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[Trigger1] ON [dbo].[SignOff] AFTER UPDATEASBEGIN TRY BEGIN TRANSACTIONUPDATE School SET School.DateTravelPlanSubmitted = SchoolSignDate.SignoffSchoolDateMaxFROM School INNER JOIN SignOff ON School.SchoolID = SignOff.SchoolIDinner join (select SignOff.SchoolID,SignoffSchoolDateMax=max(SignOff.SignedOffBySchoolDate)from SignOffinner join School on School.SchoolID=SignOff.SchoolIDgroup by SignOff.SchoolID)SchoolSignDate on SchoolSignDate.SchoolID=School.SchoolIDwhere SignOff.Submitted IS NOT NULL COMMIT TRANEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANEND CATCHI manged to copy without the where condition. Now the issue is only with the where condition. If I give as NOT NULL it is showing error.Can anyone help to fix this issue. 解决方案 Assuming that rest of your trigger is working fine the problem seems to be in the where condition.where SignOff.Submitted!=NOT NULLThe correct way of doing same iswhere SignOff.Submitted is NOT NULLChange the condition and try again 这篇关于SQl查询使用字段的非null的where条件更新另一个表中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！