Archives

The List

« MySQL Pop Quiz #6 | Main | MySQL Pop Quiz #8 »

MySQL Pop Quiz #7

By Carsten | February 25, 2008

Spanish flag Spanish translation by Marcos Besteiro

Today’s entry is from an anonymous contributor. Keep ‘em coming!Suppose you issue the following list of commands:

CREATE TABLE fk_relations (
  key1 INT NOT NULL PRIMARY KEY,
  key2 INT NOT NULL UNIQUE
);
INSERT INTO fk_relations VALUES (1,1), (2,2);

The table now contains (1,1) and (2,2). We then do

REPLACE INTO fk_relations VALUES (1,3);

Question 1: The table now contains (1,3) and (2,2). Explain why.
We now do:

REPLACE INTO fk_relations VALUES (1,2);

Question 2: The table now contains the single row (1,2). Why?

Question 3: What is the value of Rows affected in your client after each of the two REPLACE statements?

Answer 1

Answer 2

Answer 3

If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting. More Pop Quiz entries.

Topics: MySQL, Pop Quiz |

3 Responses to “MySQL Pop Quiz #7”

  1. fenixshadow Says:
    February 25th, 2008 at 5:36 pm

    The first replace statment is very complex in oracle:

    CREATE TABLE fk_relations (
    key1 INT NOT NULL PRIMARY KEY,
    key2 INT NOT NULL UNIQUE
    );

    INSERT INTO fk_relations
    VALUES (1, 1);

    INSERT INTO fk_relations
    VALUES (2, 2);

    MERGE INTO fk_relations a
    USING (SELECT 1 key1, 3 key2
    FROM DUAL) s
    ON (a.key1 = s.key1)
    WHEN MATCHED THEN
    UPDATE
    SET a.key2 = s.key2
    ;

    but oracle will negative the second replace statment:

    MERGE INTO fk_relations a
    USING (SELECT 1 key1, 2 key2
    FROM DUAL) s
    ON (a.key1 = s.key1)
    WHEN MATCHED THEN
    UPDATE
    SET a.key2 = s.key2
    ;
    ORA-00001: unique constraint (SYS.SYS_C004026) violated

    I like oracle’s solution. It’s strict rule may avoid some mistakes.

  2. Propiedad Privada » Blog Archive » Test rápidos de MySQL - Test 7 Says:
    March 25th, 2008 at 10:43 am

    [...] Traducido con permiso expreso de Mysql Test Quiz 7 Original [...]

  3. Guerrero de Terracota » MySQL Pop Quiz #7 Says:
    March 31st, 2008 at 9:08 am

    [...] ???????? ????? ?3 ????? ?3: ??? ?????? ???? ????????? ??? ?????? REPLACE (???? ??????? ? ???? ?????????). ? ??? ?????? ???? ????????????? ??? ?????? REPLACE (??? ??????? ? ???? ?????????). ????? ????????? ?????????? ? REPLACE ?? ?????? ????? ?????. [...]

Comments