Update or insert record using 1 statement and 2 MYSQL clauses

Question

Update or insert record using 1 statement and 2 MYSQL clauses

Navigation

#1 by (0 votes)

1

I have a table with the columns id_autor , id_publicacion and fecha , what I would like is that when the id_autor and the id_publication are equal to the values that happened to them, update fecha , if Both are different than insert. Thank you very much in advance for your help!

Clarifications:

The table has no primary key
The conditions that must be given to update is that both id_autor as id_publicacion are equal to X

mysql

asked by Patricio 27.11.2016 в 18:29

source

1 answer

Calculate and print average grades in subjects associated with students. (Printing of tickets per student in batch) How to use an image with spaces in HTML5 [closed]

score 0 · Accepted Answer

Example of 14.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax :

mysql> DROP TABLE IF EXISTS 'test_table';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS 'test_table' (
    ->   'id_autor' INT UNSIGNED NOT NULL, 
    ->   'id_publicacion' INT UNSIGNED NOT NULL,
    ->   'fecha' DATETIME NOT NULL,
    ->   PRIMARY KEY ('id_autor', 'id_publicacion')
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO 'test_table' ('id_autor', 'id_publicacion', 'fecha')
    -> VALUES (1, 1, NOW())
    ->   ON DUPLICATE KEY UPDATE 'fecha' = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   'id_autor',
    ->   'id_publicacion',
    ->   'fecha'
    -> FROM
    ->   'test_table';
+----------+----------------+---------------------+
| id_autor | id_publicacion | fecha               |
+----------+----------------+---------------------+
|        1 |              1 | 2000-01-01 00:00:06 |
+----------+----------------+---------------------+
1 row in set (0.00 sec)

mysql> DO SLEEP(2);
Query OK, 0 rows affected (2.00 sec)

mysql> INSERT INTO 'test_table' ('id_autor', 'id_publicacion', 'fecha')
    -> VALUES (1, 2, NOW())
    ->   ON DUPLICATE KEY UPDATE 'fecha' = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'test_table' ('id_autor', 'id_publicacion', 'fecha')
    -> VALUES (2, 1, NOW())
    ->   ON DUPLICATE KEY UPDATE 'fecha' = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'test_table' ('id_autor', 'id_publicacion', 'fecha')
    -> VALUES (1, 1, NOW())
    ->   ON DUPLICATE KEY UPDATE 'fecha' = NOW();
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT
    ->   'id_autor',
    ->   'id_publicacion',
    ->   'fecha'
    -> FROM
    ->   'test_table';
+----------+----------------+---------------------+
| id_autor | id_publicacion | fecha               |
+----------+----------------+---------------------+
|        1 |              1 | 2000-01-01 00:00:08 |
|        1 |              2 | 2000-01-01 00:00:08 |
|        2 |              1 | 2000-01-01 00:00:08 |
+----------+----------------+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO 'test_table' ('id_autor', 'id_publicacion', 'fecha')
    -> VALUES (1, 1, NOW());
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'