Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What is the default value of prev_a and prev_b when you INSERT into tb ??? –  RolandoMySQLDBA Aug 4 '11 at 19:22
    
the default value is 0.0 –  run Aug 4 '11 at 20:04
add comment

1 Answer

up vote 1 down vote accepted

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 !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.