MYSQL INSERT或UPDATE IF [英] MYSQL INSERT or UPDATE IF

问题描述

现在看了一会儿，似乎没有什么类似于我想要的东西。我知道这与我写查询的方式有关，但任何帮助将不胜感激。

我想要做的基础是：

• 如果不存在，将一些项目插入表格


• 如果项目存在， / li>
  

以下列格式存在：

名称，条形码， ，位置，价格和日期

名称 - 可用于多行
barcode - 用于特定项目，但可用作多个位置
item - 与条形码相同，但包含名称
数量 - 自解释
位置 - 这可以是不同的位置
price - 附加到特定的项目
date - 上次购买该项目

棘手的事情是，名称可以在不同的位置为不同的价格提供多个项目（条形码和项目）。这个想法是，客户可以看到他们在设定的时间买了一件物品，所以他们知道他们需要卖多少。

但是，他们购买的价格可能有所不同，因此如果价格与之前的购买不同，他们需要在表格中创建另一行。

整个事情背后的想法是记录每个地点每个项目的名称有多少，然后记录它们在上次购买时的价格。 / p>

希望有意义。

在伪装代码中：

 如果不存在则插入表
  - 名称，条形码，项目，数量，位置，价格和日期
如果名称，条形码，项目，价格相同
  - 更新数量和日期（如果更新）
  

解决方案

首先，在名称，条形码，项目，位置和价格上添加 UNIQUE 约束。

  ALTER TABLE tableX 
 ADD CONSTRAINT tableX_UQ 
 UNIQUE（name，barcode，item，location，price）;然后你可以使用 INSERT INTO ... ON DUPLICATE KEY UPDATE  
 
 
 $ b ：
  INSERT INTO tableX 
（名称，条形码，项目，位置，价格，日期）
值
（？，？，？，？，？，？，？）
在重复键更新
数量=日期
 THEN数量+值（数量） - 添加数量
 ELSE数量 - 或者保持原样
 END 
，date = CASE WHEN VALUES（date）> date 
 THEN VALUES（date）; - 将日期设置为新日期
 ELSE日期 - 或者保持原样不变
 END 
  
 
 $ b b 
 
 
 
 也可以使用REPLACE ，但是行为有所不同（如果你有外键， 。有关详情，请参阅此问题INSERT IGNOREvsINSERT ... ON DUPLICATE KEY UPDATE和@Bill Kawin的回答，讨论了  INSERT IGNORE  ，  INSERT ... ON DUPLICATE KEY  和  REPLACE  。
 
Been looking around the web for a while now and don't seem to be able to come across anything similar to what I want. I know it's something to do with the way I'm writing my query but any help would be appreciated.

The basics of what I am trying to do is:


Insert some items into a table if it does not exist
Update an item if it does exist 


It exists in the format:

name, barcode, item, quantity, location, price and date

name - can be used in several rows
barcode - is for a specific item but can be used as several locations
item - is the same as barcode but contains the name
quantity - self explanatory
location - this can be different locations
price - that is attached to a specific item
date - last time that item was purchased

The tricky thing is, a "name" can have several items (barcode and item) at different locations for different prices. The idea is that a customer can see how much they bought an item for at a set time, so they know how much they would need to sell it for. 

However the price that they bought it at can vary so they need to create another row in the table if the price is different from a previous purchase.

The idea behind the whole thing is for it to record how much a "name" has of each item at each location and then the price they bought it at and when they last purchased it.

Hope that makes sense.

In psuedo code:
    Insert into table if does not exist
    - name, barcode, item, quantity, location, price and date
    If name, barcode, item, location and price are the same
    - Update quantity and date (if more recent)

解决方案
First, add a UNIQUE constraint on name, barcode, item, location and price.
ALTER TABLE  tableX
  ADD CONSTRAINT tableX_UQ
    UNIQUE (name, barcode, item, location, price) ;
Then you can use INSERT INTO ... ON DUPLICATE KEY UPDATE: 
INSERT INTO tableX
  (name, barcode, item, location, price, quantity, date)
VALUES
  (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  quantity = CASE WHEN VALUES(date) > date
               THEN quantity + VALUES(quantity)        -- add quantity
               ELSE quantity                           -- or leave as it is
             END
, date = CASE WHEN VALUES(date) > date
               THEN VALUES(date) ;                     -- set date to new date
               ELSE date                               -- or leave as it is
             END 




REPLACE could also be used but there are differences in the behaviour (which especially matter if you have foreign keys). For details, see this question "INSERT IGNORE" vs "INSERT … ON DUPLICATE KEY UPDATE" and the answer by @Bill Kawin which discusses the differences between INSERT IGNORE, INSERT ... ON DUPLICATE KEY and REPLACE.

                        
这篇关于MYSQL INSERT或UPDATE IF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！