如何更新mysql中的行并插入新记录?

I have a mysql table, columns:

我有一个mysql表，列:

employee_id (PK), city, start_date(PK), end_date

"0123", "Boston", 01Jan2010, 01Jan2099

employee moves to another city, so a new record must be entered to table, and previous end_date should be updated.

员工移动到另一个城市，因此必须向表输入新的记录，并且应该更新以前的end_date。

"0123", "Boston", 01Jan2010, 08Sep2013
"0123", "Detroit", 08Sep2013, 01Jan2099

What is the most appropriate sql statement to do this?

最合适的sql语句是什么?

1 个解决方案

#1

You can do the update in two queries or wrap it inside a stored procedure.

您可以在两个查询中进行更新，或者将其封装到存储过程中。

First, update the current end_date of the person.

首先，更新person的当前end_date。

UPDATE  tableName
SET     end_date = DATE_FORMAT(CURDATE(), '%d%b%Y')
ORDER   BY STR_TO_DATE(start_date, '%d%b%Y') DESC
LIMIT   1

Second, insert new record.

第二,插入新记录。

INSERT INTO tableName(employee_id, city, start_date, end_date)
VALUES('0123', 'Detroit', DATE_FORMAT(CURDATE(), '%d%b%Y'), '01Jan2099')

Here's a stored procedure,

这是一个存储过程,

DELIMITER ;;
CREATE PROCEDURE EmployeeRecord
(
    IN empID VARCHAR(15),
    IN newLoc VARCHAR(30)
)
BEGIN
    UPDATE  tableName
    SET     end_date = DATE_FORMAT(CURDATE(), '%d%b%Y')
    WHERE   employee_id = empID
    ORDER   BY STR_TO_DATE(start_date, '%d%b%Y') DESC
    LIMIT   1;

    INSERT INTO tableName(employee_id, city, start_date, end_date)
    VALUES(empID, newLoc, DATE_FORMAT(CURDATE(), '%d%b%Y'), '01Jan2099');
END ;;
DELIMITER ;

USAGE:

用法:

CALL EmployeeRecord('0123','Detroit')

As a sidenote, please use DATE or DATETIME datatype when storing dates to avoid the use of function on a field which kills the index if there is any.

作为一个sidenote，请在存储日期时使用DATE或DATETIME datatype，以避免在某个字段上使用函数，如果有任何函数，则会导致索引失效。

#1