如果id存在则更新，否则INSERT（ODBC）

I'm using a PHP script to connect to a microsoft access database using ODBC

我正在使用PHP脚本使用ODBC连接到Microsoft Access数据库

The table I'm working on has an 'id' column. I'd like to INSERT new records if the SKU doesn't already exist, else if the SKU does exist, simply UPDATE that record.

我正在处理的表格有一个'id'列。如果SKU尚不存在,我想插入新记录,否则如果SKU确实存在,只需更新该记录。

What is the correct SQL for this? Is it possible to do it in one query?

什么是正确的SQL?是否可以在一个查询中执行此操作?

3 个解决方案

#1

Personally, I would leave that to the application logic rather than the database logic. You can simply do:

就个人而言,我会将其留给应用程序逻辑而不是数据库逻辑。你可以简单地做:

if($id)
{
    $sql = 'UPDATE ... WHERE id = ?'; // assuming use of parameterized query
}
else
{
    $sql = 'INSERT INTO ...';
}

#2

I would rather do it in 2 queries.

我宁愿在2个查询中做到这一点。

For me update or insert they are different BUSINESS logics. To get your code clear and clean it's better you explicitly define the logic for INSERT and UPDATE.

对我来说,更新或插入它们是不同的商业逻辑。为了使代码清晰明了,最好明确定义INSERT和UPDATE的逻辑。

And technically I don't see a Clean way doing it in one SQL statement. Each of statement has to start with a "verb" it's either "UPDATE" or "SELECT" or "DELETE" or something else. Or if you insist to do so, you can try delete the potential existing one before you are doing "insert", then you do the "insert" anyway, but then maybe you get a inconsistent Id for same entity, which could cause problems too.

从技术上讲,我没有在一个SQL语句中看到一种干净的方式。每个语句都必须以“动词”开头,它是“UPDATE”或“SELECT”或“DELETE”或其他内容。或者如果你坚持这样做,你可以尝试删除潜在的存在,然后你做“插入”,然后你做“插入”,但是然后你可能会得到同一实体的不一致的Id,这也可能导致问题。

Or if "id" is primary key, another silly way is to do an "update" anyway, then do "insert" anyway. If there's no valid id, update will fail, and insert will succeed. If there's valid id, update will succeed and insert will fail. You'll get it done without the BUSINESS if-else logic, but everytime there'll be a SQL failure, which IS silly and ugly.

或者如果“id”是主键,另一种愚蠢的方式是进行“更新”,然后再进行“插入”。如果没有有效的ID,则更新将失败,并且插入将成功。如果有有效的id,则update将成功,insert将失败。你可以在没有BUSINESS if-else逻辑的情况下完成它,但每次都会出现SQL故障,这很愚蠢。

That's pretty much what I can think of.

这几乎是我能想到的。

#3

I wouldn't suggest doing it in one query. Check if the value you want to add exists before you do the query to add a new value.

我不建议在一个查询中执行此操作。在执行查询以添加新值之前,请检查是否存在要添加的值。

#1