MySql IF存在时选择ELSE插入 [英] MySql IF exists select ELSE insert

问题描述

我已经为此受了两个小时的痛苦.我想选择或插入一条记录.如果记录存在，请选择其ID，否则将其插入并获取新插入的ID.现在，我正在尝试运行此程序，但仍然出现错误

I have been suffering at this for two hours now. I want to select or insert a record. If the record exist select his ID else insert it and get the new inserted ID. For now I'm trying to run this but I still get an error

SELECT CASE WHEN (SELECT COUNT(*) FROM Domains WHERE Domain = @domain)>0 
THEN 
    (SELECT Domain_ID FROM Domains WHERE Domain = @domain) 
ELSE        
    INSERT INTO Domains(Domain_ID,Domain,Disabled,Description) VALUES(@Domain_ID,@Domain,@Disabled,@Description);

推荐答案

您的案例缺少结尾

Case when (something) then (Some)
else (another thing) end

无论如何，您的其他人必须返回一个选择，插入将不返回任何内容. 如果要插入(如果不存在)，然后返回插入的值(或返回，如果存在) 做到这一点:

Anyway, your else must return a select, an insert won't return anything. if you want to insert if not exist and then return the value inserted (or return if exists) do this:

INSERT INTO Domains(Domain_ID,Domain,Disabled,Description) VALUES(@Domain_ID,@Domain,@Disabled,@Description) where not exists (select 1 from Domains WHERE Domain = @domain);

SELECT Domain_ID FROM Domains WHERE Domain = @domain

这将插入，如果不存在，然后返回.如果已经存在，将不会插入并返回值

This will insert if not exists, and then return. If already exists, won't insert, and return the value

编辑2016年1月

最后一个查询不适用于MySql，它是MSSQL语法

The last query won't work on MySql, it is a MSSQL syntax

这篇关于MySql IF存在时选择ELSE插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！