如果行存在则更新，否则将新行插入另一个表 [英] Update if row exists, otherwise insert new row into another table

问题描述

我知道这个问题但它没有帮助.

我有一个包含 ID 和值的列表.现在我必须检查我的用户数据库中是否存在该 id.如果是，则该行将被更新，否则我必须在另一个表中插入 id (tmp_user).

I have a list with ids and values. Now I have to check if the id exists in my user database. If it does, then the row will be updated, otherwise I have to insert the id in another table (tmp_user).

这是我的尝试

IF NOT EXISTS (SELECT * FROM `wcf1_user` WHERE `steamID` = 1) THEN

INSERT INTO `wcf1_points_tmp` (`steamID`, `points`) VALUES (1, 2)

ELSE

// Update stuff......

END IF;

结果:#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在IF NOT EXISTS (SELECT * FROM wcf1_user WHERE steamID = 1) THEN INSERT IN"附近使用的正确语法第 1 行

Result: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM wcf1_user WHERE steamID = 1) THEN INSERT IN' at line 1

感谢您的帮助.:)

推荐答案

如果指定 ON DUPLICATE KEY UPDATE，并且插入的行会导致 UNIQUE 索引或 PRIMARY 中的重复值 KEY，执行旧行的UPDATE.例如，如果列 a 声明为 UNIQUE 并包含值 1，则以下两个语句具有相同的效果:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

这篇关于如果行存在则更新，否则将新行插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！