Delete from table using PRIMARY KEY mysql

Discussion in 'MySQL' started by neha2011, Jun 26, 2011.

0
  1. #1
    How would you use PRIMARY KEY in a delete query?

    even though i know the primary key name, i want to be able do this:

    delete FROM games WHERE 'PRIMARY KEY'=10242

    it works but it doesnt delete the row.
     
    neha2011, Jun 26, 2011 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Use back ticks (`) instead of single quotes around primary key.
     
    mwasif, Jun 27, 2011 IP
  3. mephisto

    mephisto Active Member

    Messages:
    125
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    58
    #3
    Then it doesn't work. What stops you from using the name of the primary key?
     
    mephisto, Jun 30, 2011 IP
  4. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #4
    You need to use the name of the field that is the primary key. God help us all if it is actually named 'PRIMARY KEY'.
     
    plog, Jun 30, 2011 IP
  5. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #5
    As for the reason why it doesnt work but doesnt throw an error either, WHERE 'PRIMARY KEY'=10242 compares a string PRIMARY KEY to a number 10242 which gives false irrespective of what is present in the table. As suggested before use `PRIMARY KEY` instead

    And I would assume this wouldnt be possible due to being reserves but i tried and it is indeed possible!

     
    iama_gamer, Jul 6, 2011 IP
  6. Warrichpk

    Warrichpk Banned

    Messages:
    192
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    28
    #6
    hello neha2011 i will tell you how it works
    suppose you r having a table
    +------+------------+-----------+------------+------------+---------+-----------+-------------+
    | id | first_name | last_name | start_date | end_date | salary | city | description |
    +------+------------+-----------+------------+------------+---------+-----------+-------------+
    | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
    | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
    | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
    | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
    | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
    | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
    | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
    | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
    +------+------------+-----------+------------+------------+---------+-----------+-------------+
    and here first name and last name is primary key
    then if yo u want to delet this
    type > ALTER TABLE Employee DROP PRIMARY KEY;
    it will work now

    +-----+---------+------------+-------+---------+----------
    | id | start_date | end_date | salary | city | description |
    +-----+----------+-----------+---------+--------+------------
    | 1 |1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer
    | 2 | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester
    | 3 | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
    | 4 |1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager
    | 5 |1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester
    | 6 |1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester
    | 7 | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager
    | 8 | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester
    +---------+------------+------------+---------+-----------+-----
    you will get like this
    Try it
     
    Warrichpk, Jul 11, 2011 IP