Как выполнить UPSERT, чтобы я мог использовать как новые, так и старые значения в части обновления

Глупый, но простой пример: Предположим, у меня есть таблица "Item", где я сохраняю итоговые значения получаемых элементов.

Item_Name              Items_In_Stock

Здесь указывается имя элемента. Как достичь следующего, когда когда-либо получаю элемент А в количестве X.

Если элемент не существует, я вставляю новый реконструированный для Item A и устанавливаю элементы в запасе на X и если существует запись, в которой позиции на складе были Y, тогда новое значение в позициях на складе равно (X + У)

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = 27 + (SELECT items_in_stock where item_name = 'A' )

Моя проблема в том, что у меня есть несколько столбцов в моей фактической таблице. Это хорошая идея написать несколько операторов select в части обновления?

Конечно, я могу сделать это в коде, но есть ли лучший способ?

Ответ 1

Как уже упоминалось в моем комментарии, вам не нужно делать подзаголовок, чтобы ссылаться на строку, вызывающую включение ключа DUPLICATE KEY. Итак, в вашем примере вы можете использовать следующее:

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27

Помните, что большинство вещей действительно просты, если вы поймаете себя на чрезмерном компрометировании чего-то, что должно быть простым, то вы, скорее всего, сделаете это неправильно:)

Ответ 2

Вы можете получить представление из этого примера:

Предположим, вы хотите добавить пользовательские данные за семь дней.

Он должен иметь уникальное значение для идентификатора пользователя и дня, например

UNIQUE KEY `seven_day` (`userid`,`day`)

Вот таблица

CREATE TABLE `table_name` (
  `userid` char(4) NOT NULL,
  `day` char(3) NOT NULL,
  `open` char(5) NOT NULL,
  `close` char(5) NOT NULL,
  UNIQUE KEY `seven_day` (`userid`,`day`)
);

И ваш запрос будет

INSERT INTO table_name (userid,day,open,close) 
    VALUES ('val1', 'val2','val3','val4') 
        ON DUPLICATE KEY UPDATE open='val3', close='val4';

Пример:

<?php
//If your data is
$data= array(
        'sat'=>array("userid"=>"1001", "open"=>"01.01", "close"=>"11.01"),
        'sun'=>array("userid"=>"1001", "open"=>"02.01", "close"=>"22.01"),
        'sat'=>array("userid"=>"1001", "open"=>"03.01", "close"=>"33.01"),
        'mon'=>array("userid"=>"1002", "open"=>"08.01", "close"=>"08.01"),
        'mon'=>array("userid"=>"1002", "open"=>"07.01", "close"=>"07.01")
    );


//If you query this in a loop
//$conn = mysql_connect("localhost","root","");
//mysql_select_db("test", $conn);

foreach($data as $day=>$info) {
    $sql = "INSERT INTO table_name (userid,day,open,close) 
                VALUES ('$info[userid]', '$day','$info[open]','$info[close]') 
            ON DUPLICATE KEY UPDATE open='$info[open]', close='$info[close]'";
    mysql_query($sql);
}
?>

Ваши данные будут в таблице:

+--------+-----+-------+-------+
| userid | day | open  | close |
+--------+-----+-------+-------+
| 1001   | sat | 03.01 | 33.01 |
| 1001   | sun | 02.01 | 22.01 |
| 1002   | mon | 07.01 | 07.01 |
+--------+-----+-------+-------+

Ответ 3

Хотя ответ михаэля правильный, вам нужно знать немного больше, чтобы выполнить установку программно:

Сначала создайте свою таблицу и укажите, для каких столбцов требуется уникальный индекс:

CREATE TABLE IF NOT EXISTS Cell (
  cellId BIGINT UNSIGNED,
  attributeId BIGINT UNSIGNED,
  entityRowId BIGINT UNSIGNED,
  value DECIMAL(25,5),
  UNIQUE KEY 'id_ce' ('cellId','entityRowId')
)

Затем вставьте в него некоторые значения:

INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );

Попробуйте сделать то же самое снова, и вы получите ошибку дублированного ключа, потому что cellId и entityRowId одинаковы:

INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );

Повторяющаяся запись "1-199" для ключа "id_ce"

Вот почему мы используем команду upsert:

INSERT INTO Cell ( cellId, attributeId, entityRowId, value)
VALUES( 1, 6, 199, 300.0 )
ON DUPLICATE KEY UPDATE 'value' = 'value' + VALUES('value')

Эта команда принимает значение 1.0, которое уже существует, в качестве значения и выполняет value = value + 300.0.

Таким образом, даже после выполнения вышеупомянутой команды в таблице будет только одна строка, и значение будет 301.0.

Ответ 4

Это синтаксис для upsert

INSERT INTO `{TABLE}` (`{PKCOLUMN}`, `{COLUMN}`) VALUES (:value)
ON DUPLICATE KEY UPDATE `{COLUMN}` = :value_dup';

Ответ 5

Если у вас есть значение для столбца PK или уникального индекса для столбца, который удовлетворяет уникальности, вы можете использовать INSERT IGNORE, INSERT INTO ... ON DUPLICATE или REPLACE

Пример с INSERT IGNORE

INSERT IGNORE INTO Table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (....);

Пример с INSERT INTO .. ON DUPLICATE KEY UPDATE

SET @id = 1,
    @serverId = 123545,
    @channelId = 512580,
    @channelRole = 'john';
INSERT INTO Table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (@id, @serverId, @channelId, @channelRole)
ON DUPLICATE KEY UPDATE
    serverId = @serverId,
    channelId = @channelId,
    channelRole = @channelRole;

Пример с Replace

REPLACE INTO table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (...);