SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update statement has no effect

    Hi

    I have the following table:

    CREATE TABLE image_info (
    Image_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Place VARCHAR(255) NOT NULL,
    Date VARCHAR(255) NOT NULL,
    Description VARCHAR(255) NOT NULL,
    Submitter INT UNSIGNED NOT NULL,
    Submission_Date DATE default '0000-00-00',
    PRIMARY KEY (Image_ID),
    FOREIGN KEY Submitter_fk (Submitter) REFERENCES people(AccountID)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1;

    Just for clarity:
    * Date: The date the photo was taken (it may be a free text, so it needs to be a VARCHAR(255) and not a Date
    * Submission_Date: The date the photo was uploaded

    The table is populated using:
    LOAD DATA INFILE 'ImageInfo.sql' INTO TABLE image_info;

    After the table is populated, I try to perform the following update statement:
    UPDATE image_info SET Date = '2004-05-30', Submitter = 1, Submission_Date = CURDATE() WHERE Image_ID = 1041;

    It is seems like the statement has no effect because the appropriate row isn't changed.
    What could be the problem?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try backticks around the reserved word DATE

    also, please show the results of
    Code:
    SELECT * FROM image_info WHERE Image_ID = 1041
    (one of the few times it's okay to use "select star")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the result of the query you requested:

    +----------+----------------------------+------------------+------+-------------+-----------+-----------------+
    | Image_ID | Title | Place | Date | Description | Submitter | Submission_Date |
    +----------+----------------------------+------------------+------+-------------+-----------+-----------------+
    | 1401 | Rachel baby in bed 3 | Potomac, MD | | | 0 | 0000-00-00 |
    +----------+----------------------------+------------------+------+-------------+-----------+-----------------+

    what do you mean using backticks? Can you give an example:
    Is this what you have in mind:

    UPDATE image_info SET `Date` = '2004-05-30', Submitter = 1, Submission_Date = CURDATE() WHERE Image_ID = 1041;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •