SQL-通过检查两列进行UPDATE或INSERT [英] SQL - UPDATE or INSERT by checking two columns

问题描述

我对SQL有点陌生，所以我希望这不会成为一个愚蠢的问题。我正在尝试创建一个调度程序，其中的表是这样的：

I'm somewhat new to SQL so I hope this doesn't come off as a dumb question. I am trying to create a scheduling program where the table is as such:

event_id    worker_id    date            shift_type
1           1            2014-01-01      Labour Day Shift
2           1            2014-01-02      Labour Night Shift
3           2            2014-01-01      Engineer Day Shift
4           2            2014-01-02      Engineer Night Shift
5           3            2014-01-01      Electrician Day Shift
6           3            2014-01-02      Electrician Day Shift

如您所见，我将每个班次都视为一个事件。当我插入数据库时​​，我想要一种方法来检查同一行下表中是否已经存在 worker_id和 date，如果是，则使用UPDATE而不是INSERT。请注意，'worker_id'或'date'都不是唯一的，但是，两者在一行中的组合将是唯一的。

As you can see, I treat each "shift" as an event. When I insert into the database, I would like a way to check if 'worker_id' and 'date' already exist in the table under the same row and if so use UPDATE rather then INSERT. Please note, that neither 'worker_id' or 'date' is unique, however the combination of the two in a single row would be unique.

推荐答案

您要重复键更新。但是首先，您需要在 worker_id 和 date 上具有唯一索引：

You want on duplicate key update. But first, you need a unique index on worker_id and date:

create unique index idx_table_worker_date on table(worker_id, date);

insert into table(worker_id, date, shift_type)
    values (@worker_id, @date, @shift_type)
    on duplicate key update shift_type = values(shift_type);

表是表的占位符名称。以 @ 开头的变量是要插入的值的占位符。

The table is a placeholder for your table name. The variables starting with @ are placeholders for the values you want to insert.

这篇关于SQL-通过检查两列进行UPDATE或INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！