Ignore On Update behavior for some queries

Help
1

Hi,

I’m trying to figure out how to potentially not have my timestamp column not get updated for a special case.

Quick high level of workflow:

  1. External process updates records in table.
  2. Internal process queries table for all changes base on the modified date timestamp
  3. Then process the records, push them back into the db updating some data

Now the issue is in step 2, I would query the timestamp to get the updated records, but in step 3 I’m also updating the records unless the record didn’t change. Step 2 would pick it up again.

How can I still update the record but have the modified date timestamp not get updated?

The doc mention including the modified date timestamp column in the update statement would ignore but I’m struggling on a clean way to put a good value.

I could join to the table itself to extract the current timestamp but that seems slow and hacky. Alternatively I could use the timestamp when I first read the record but then the update would not be atomic.

Ideally I’d want to use the VALUES() function but it seems to only work on ON DUPLICATE KEY statements.

Any insight here would greatly be appreciated.

Thanks,
Oak

2

Hi Oak! :wave: Happy to help with your issue. Can you tell us if you are on the managed or self-hosted service and what version you are running?

3

Hi @MariaSilverhardt,

I’m on the managed service running on 7.6.16.

1 Like
4

Maybe something like this:

CREATE TABLE s2(id INT, ts DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6));
INSERT INTO s2(id) VALUES(1);
INSERT INTO s2(id) VALUES(2);

id,ts
1,2023-07-30 18:48:28.148222
2,2023-07-30 18:48:28.171480

External process does the update like this:

// ts is automatically updated with default.
UPDATE s2
SET id = 3
WHERE id = 2

id,ts
1,2023-07-30 18:48:28.148222
3,2023-07-30 18:49:07.909945

Internal process does the update like this:

// Explicit setting of ts to its current value overrides default behavior.
UPDATE s2
SET id = 4, ts = ts
WHERE id = 3

id,ts
1,2023-07-30 18:48:28.148222
4,2023-07-30 18:49:07.909945