REPLACE INTO without affecting certain columns?

Databases
1

Hi there :slight_smile:

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

Ahhh, looks like I may have posted too soon. ON DUPLICATE KEY UPDATE seems to do the trick:

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'