前段时间通过mybatis写sql,想实现EXISTS语法,一直报错,记录一下,以防以后范同样的错误,
错误语法类似如下:
INSERT INTO [dbo].[geo_asso_type] ([geo_asso_type_id] ,[bound_asso_type] ,[updated_date]) VALUES (11 ,‘Province to City‘ ,GETDATE() WHERE NOT EXISTS (SELECT 1 FROM [dbo].[geo_asso_type] WHERE [geo_asso_type_id] = 11)
正确语法记录一下
第一种:前置not exists
IF NOT EXISTS (SELECT 1 FROM [dbo].[geo_asso_type] WHERE [geo_asso_type_id] = 11) BEGIN INSERT INTO [dbo].[geo_asso_type] ([geo_asso_type_id] ,[bound_asso_type] ,[updated_date]) VALUES (11 ,‘Province to City‘ ,GETDATE()) END
第二种:通过select的方式插入数据
INSERT INTO [dbo].[geo_asso_type] ( [geo_asso_type_id], [bound_asso_type], [updated_date] ) SELECT 11, ‘Province to City‘, GETDATE() WHERE NOT EXISTS( SELECT 1 FROM [dbo].[geo_asso_type] WHERE [geo_asso_type_id] = 11 )