dongpan1308
dongpan1308
2017-03-20 08:21

在SQL中删除或更新重复值

已采纳

I have put a unique index on my database table for steamid. I am unsure how I can insert a record with duplicate values for this key (dropping the old record) without getting an error. Could someone edit my code to make it suitable, Thanks.

$sql = "INSERT INTO steam_data (reg_date, reg_year, steamname, steamid) VALUES ('$dateoutput', '$dateoutputyear', '$personaname', '$steamid')";

The error is :

Error: INSERT INTO steam_data (reg_date, reg_year, steamname, steamid) VALUES ('March 20, 07:09pm', '2017', 'Aweosmedude', '76561113498123831721') Duplicate entry '76561113498123831721' for key 'steamid'

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • doujianqin5172 doujianqin5172 4年前

    It means you already have a record in your steam_data table with streamid=76561113498123831721; and you have a constraint in your schema that ensures that you don't have more than one streamid. So you can either do INSERT IGNORE INTO stream_data... or do INSERT INTO stream_data...ON DUPLICATE KEY...

    e.g.

    INSERT INTO steam_data (reg_date, reg_year, steamname, steamid) VALUES ('$dateoutput', '$dateoutputyear', '$personaname', '$steamid') ON DUPLICATE KEY UPDATE steamname=VALUES(steamname);

    (@ReneS answer has example of INSERT IGNORE INTO...)

    点赞 评论 复制链接分享
  • duanliaolan6178 duanliaolan6178 4年前

    One more option is the REPLACE statement:

    REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

    REPLACE INTO steam_data (reg_date, reg_year, steamname, steamid) VALUES ('$dateoutput', '$dateoutputyear', '$personaname', '$steamid')
    

    (I sure hope you're not actually inserting data into your database with a query like that. Use prepared statements!)

    点赞 评论 复制链接分享
  • dongxi7704 dongxi7704 4年前
    $sql = "INSERT IGNORE INTO steam_data (reg_date, reg_year, steamname, steamid) VALUES ('$dateoutput', '$dateoutputyear', '$personaname', '$steamid')";
    
    点赞 评论 复制链接分享