将 INSERT 命令转换为 UPDATE 命令
我有两个 INSERT 命令，这对我来说毫无用处，因为两组行 - 已经在表中的行和我作为 INSERT 的行命令 - 不是分离的。这两个命令都插入大量行和大量值。
因此，如果我想执行这些行，我会收到重复条目错误。
有没有简单的方法可以将这些命令“转换”为 UPDATE？
我知道这听起来很愚蠢，因为如果我想UPDATE，为什么我要发出INSERT命令。只是为了使其成为一个清晰的场景：另一位开发人员给了我脚本:)
提前致谢， 丹尼尔
编辑 - 问题已解决
首先我创建了一个表并用我的INSERT命令填充它，然后我使用了以下REPLACE命令：
REPLACE
INTO table_1
SELECT *
FROM table_2;
这可以最初位于：如何合并两个 MySQL 表？
I have two INSERT commands, that are useless to me like that because the two sets of rows - the ones that are already in the table, and the ones I have as INSERT commands - are not disjunct. Both commands insert lots of rows, and lots of values.
Therefore I get the duplicate entry error if I want to execute those lines.
Is there any easy way to 'convert' those commands into UPDATE?
I know this sounds stupid, because why do I make INSERT commands, if I want to UPDATE. Just to make it a clear scenario: another developer gave me the script:)
Thanks in advance,
Daniel
EDIT - problem solved
First I created a table and filled it up with my INSERT commands, then I used the following REPLACE command:
REPLACE
INTO table_1
SELECT *
FROM table_2;
This can originally be found at: How can I merge two MySQL tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
MySQL 的
REPLACE关键字可以做到这一点。只需将查询中的INSERT关键字替换为REPLACE一词，它就会更新行而不是插入新行。请注意，它仅在您插入主键或唯一键列时才有效。MySQL's
REPLACEkeyword does this. Simply replace theINSERTkeyword in your queries with the wordREPLACEand it should update the rows instead of inserting new ones. Please note that it will only work if you're inserting a primary key or unique key column.您必须手动将它们重写为更新。如果我遇到这样的问题，我首先查询某个主键的计数，如果没有找到，我插入一个通用数据集，然后更新它。这样，可以添加新数据并更新现有数据，并且您不必区分插入新数据和更新数据。
You would have to rewrite them to updates by hand. If I encouter such a problem, I query for the count of certain primary key first, if none is found I insert a generic dataset and update it afterwards. By this, new data can be added and already existing data will be updated, and you don't have to differentiate between inserting new data and updating data.
对于 MySQL，您可以使用
INSERT IGNORE或INSERT ... ON DUPLICATE UPDATE语法。请参阅 MySQL 参考手册For MySQL, you can use either the
INSERT IGNOREor theINSERT ... ON DUPLICATE UPDATEsyntaxes. See the MySQL reference manual您可以轻松修改查询以更新重复行，请参阅 MySQL 中的 INSERT ... ON DUPLICATE KEY 语法
You can easily modify your queries to update duplicate rows, see INSERT ... ON DUPLICATE KEY syntax in MySQL