MySQL在非主密钥的重复更新上插入 [英] MySQL insert on duplicate update for non-PRIMARY key
本文介绍了MySQL在非主密钥的重复更新上插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对重复更新查询中的插入有点困惑. 我有这样的MySQL表结构:
I am little confused with insert on duplicate update query. I have MySQL table with structure like this:
- record_id(PRIMARY,唯一)
- person_id(UNIQUE)
- some_text
- some_other_text
如果我的table.person中存在id,我想更新person的some_text和some_other_text值,否则,请在此表中插入新记录.如果person_id不是PRIMARY,该怎么办?
I want to update some_text and some_other_text values for person if it's id exists in my table.person or insert new record in this table otherwise. How it can be done if person_id is not PRIMARY?
推荐答案
您需要一个查询,以检查是否存在与record_id(或person_id)相关的任何行.如果存在,请更新它,否则插入新行
You need a query that check if exists any row with you record_id (or person_id). If exists update it, else insert new row
IF EXISTS (SELECT * FROM table.person WHERE record_id='SomeValue')
UPDATE table.person
SET some_text='new_some_text', some_other_text='some_other_text'
WHERE record_id='old_record_id'
ELSE
INSERT INTO table.person (record_id, person_id, some_text, some_other_text)
VALUES ('new_record_id', 'new_person_id', 'new_some_text', 'new_some_other_text')
另一种更好的方法是
UPDATE table.person SET (...) WHERE person_id='SomeValue'
IF ROW_COUNT()=0
INSERT INTO table.person (...) VALUES (...)
这篇关于MySQL在非主密钥的重复更新上插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文