SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    REPLACE INTO without affecting certain columns?

    Hi there

    I was hoping someone could help me find a solution for this problem...

    I have a MySQL table that stores information from a 3rd party feed. Each row is associated with a member. The table looks something like this:

    record_id PK
    member_id PK and FK
    keywords
    content
    author
    is_read
    has_replied

    The record_id is a numeric ID which is supplied as part of the 3rd party feed. We don't want the same feed record to be stored more than once against each member which is why both the record_id and member_id form the Primary Key.

    Every time a member performs a certain search on our site, the list of records is updated by using a REPLACE INTO statement. We do the update because the record_id and member_id could be the same but the keywords could change. The problem with REPLACE INTO is that it deletes and re inserts all rows with a duplicate PK value which essentially overwrites any values that would have changed for is_read and has_replied. It is essential that if the values for is_read or has_replied have changed then those values remain intact for that record.

    So is there a way to use REPLACE INTO without affecting certain column values? Or do I have to look at doing something completely different?

    I can't make is_read and has_replied part of the primary key as then we'd get duplicate entries for record_id and member_id.

    Any ideas would be much appreciated.

    Many thanks,
    Simon

  2. #2
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh, looks like I may have posted too soon. ON DUPLICATE KEY UPDATE seems to do the trick:

    Code:
    INSERT INTO
            table
    	(record_id, member_id, keywords, content, author)
    VALUES
    	(a, b, c, d, e),
            (f, g, h, i, j),
            (etc etc)
    ON DUPLICATE KEY UPDATE
    	keywords = 'keywords here'


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
  •