SQL 插入多个记录/更新现有(在单个查询中) [英] SQL Inserting Multiple Records / Updates Existing (In Single Query)

问题描述

我正在尝试在单个查询中插入多个数据.

I'm trying to insert multiple data in a single query.

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1'),
    ('PENCIL',  '1'),
    ('FOLDER',  '1'),
    ('PEN',     '1'),
    ('ERASER',  '1') 

这有效.基本上它所做的只是针对每个条目(或如上所示的PAPER"、PENCIL"等项目)，它只是将其作为一个记录插入.

This works. Basically all it does is for every entry (or item such as 'PAPER', 'PENCIL', etc as seen above), it just inserts it as one record.

我现在的问题是如果记录已经存在怎么办?我不想插入与现有记录具有相同详细信息的新记录.

My issue now is what if the record is already existing? I dont want to insert a new record which has the same details as the already existing one.

我想弄清楚是如何发现重复密钥更新的.

I'm trying to figure out how and came across ON DUPLICATE KEY UPDATE.

这是我的新查询:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1'),
    ('PENCIL',  '1'),
    ('FOLDER',  '1'),
    ('PEN',     '1'),
    ('ERASER',  '1') 
ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY=QUANTITY;

尽管这对插入很有效，但我的问题是所有现有记录都将具有应更新内容的固定值，无法确定哪个记录应具有指定值作为新数据.

Although this works well with inserting, my issue is that all existing records will have a fix value of what it should be updated, not being able to determine which record should have the specified value as a new data.

不用说，使用这种方法，我无法确定应该插入什么值作为对特定记录的更新，因为它会更新所有现有数据.

Needless to say, with this approach, I am unable to determine what value should be inserted as an update to a certain record, as it does the update for all existing data.

我正在考虑一种类似的方法:

I was thinking of an approach something like:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='2',
    ('PENCIL',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='33',
    ('FOLDER',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='19',
    ('PEN',     '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='28',
    ('ERASER',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='14';

这样的东西可以帮助我将任意数量的记录插入表中，如果我尝试插入的某个记录已经存在，则将使用新数据进行更新.但是这个当然行不通.

Something like this that would help me insert whatever number of records into the table, and if a certain record that I'm trying to insert already exists, will update with a new data instead. But of course this one doesn't work.

我正在尝试找到一种方法来做这样的事情，因为在单个查询中插入多条记录是一项要求.

I'm trying to find a way how to do something like this, as inserting multiple records in a single query is a requirement.

注意:我在 MySQL 上工作.另外，我试图避免使用存储过程.

Note: I am working this on MySQL. Also, I'm trying to avoid using Stored Procedures.

推荐答案

你有一个奇怪的情况，你要更新的值没有被传入.

You have a strange situation, where the value you want to update is not being passed in.

假设 ITEM 是唯一的:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
    VALUES ('PAPER', 1),
           ('PENCIL', 1),
           ('FOLDER', 1),
           ('PEN', 1),
           ('ERASER', 1)
    ON DUPLICATE KEY UPDATE
       QUANTITY = (CASE ITEM
                       WHEN 'PAPER' THEN 2
                       WHEN 'PENCIL' THEN 33
                       WHEN 'FOLDER' THEN 19
                       WHEN 'PEN' THEN 28
                       WHEN 'ERASER' THEN 14
                   END);

这也假设 QUANTITY 是一个数字.数字常量不需要单引号.

This also assumes that QUANTITY is a number. Single quotes are not needed around numeric constants.

这篇关于SQL 插入多个记录/更新现有(在单个查询中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！