Shift db data to another column

Question

I have the following table

id  |   ev_id   | ev_loc_id |   a   |   b   | prev_a    |   prev_b

If I the (ev_id AND ev_loc_id) not exist in the table then I just insert them, otherwise I'm putting the a to the prev_a and b to prev_b.

I solved this with a selection:

SELECT a, b FROM tb WHERE ev_id=XX AND ev_loc_id=XXX

If the array is empty than I inserted the new data, otherwise I replaced the a,b and the selected values to prev_a, prev_b

Is there most time efficient solution?

Answer 1

Here is what you can do:

INSERT ... ON DUPLICATE KEY UPDATE

You can do the following

• make prev_a assume the value of a
• make prev_b assume the value of b
• make a assume the value of the new a
• make b assume the value of the new b

Here are the statements to create sample data for this test:

use test
DROP TABLE IF EXISTS ev;
CREATE TABLE ev
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Here is the output:

mysql> use test
DROP TABLE IF EXISTS ev;
Database changed
mysql> DROP TABLE IF EXISTS ev;
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE ev
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     ev_id INT NOT NULL,
    ->     ev_loc_id INT NOT NULL,
    ->     a FLOAT DEFAULT 0.0,
    ->     b FLOAT DEFAULT 0.0,
    ->     prev_a FLOAT DEFAULT 0.0,
    ->     prev_b FLOAT DEFAULT 0.0,
    ->     PRIMARY KEY (id),
    ->     UNIQUE KEY (ev_id,ev_loc_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,5.0,7.0),
    -> (1,3,9.0,3.0),
    -> (2,2,7.0,9.0),
    -> (2,3,3.0,9.0);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ev;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    5 |    7 |      0 |      0 |
|  2 |     1 |         3 |    9 |    3 |      0 |      0 |
|  3 |     2 |         2 |    7 |    9 |      0 |      0 |
|  4 |     2 |         3 |    3 |    9 |      0 |      0 |
+----+-------+-----------+------+------+--------+--------+
4 rows in set (0.00 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,8.0,4.0) ON DUPLICATE KEY
    -> UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    8 |    4 |      5 |      7 |
+----+-------+-----------+------+------+--------+--------+
1 row in set (0.00 sec)

Give it a Try !!!