How to get value before MYSQL update command?

Question

I have a line:

Update <Table> Set <Value> = @NewValue Where RecordID = @RecordID;

However, now I find myself needing to update another table with the change in this value. Obviously I could do a separate query to get the original, or I could save the original when I originally read it. The elegant answer would be to somehow return it, though.

(RecordID is unique, this won't return multiple values.)

Possible duplicate of Get OLD Value in MySQL Trigger AFTER Update Statement — Sam M
– Sam M, Commented Jul 19, 2018 at 1:04
@SamM That's talking about triggers which aren't going to work in my case--the second update(s) require access to three fields, one of which is found in this record, the others are defined in a config and their values are in yet another table. — Loren Pechtel
– Loren Pechtel, Commented Jul 19, 2018 at 1:28

Barmar · Accepted Answer · 2018-07-19 01:10:02Z

0

You can update both tables in one query

UPDATE t1
CROSS JOIN t2
SET t2.<value> = t1.<value>,
    t1.<value> = @NewValue
WHERE t1.RecordID = @RecordID1
AND t2.RecordID = @RecordID2

If the rows of the two tables are related, use an INNER JOIN instead of CROSS JOIN, and put the t2 condition there.

answered Jul 19, 2018 at 1:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Loren Pechtel Over a year ago

There almost certainly are two records in t2 that need updating (and by the difference, not merely the value I'm writing) and that's simply current usage, a change to a configuration file could change the number of records affected.

Barmar Over a year ago

You can change the t2.RecordID = @RecordID2 to any condition you want to find the affected rows.

Collectives™ on Stack Overflow

How to get value before MYSQL update command?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related