Update MySQL db if value is changed and change another value

Question

In my database I have a updateinfo with timestamp etc. which should updated every time the values has changes/update.

actually I check at first if there has changed value (old) and value (new) only if it is, I add the update value with new updateinfos.

Now I ask myself how I can do it easier and faster. Normally MySQL doesn't update something if the value has not changed. But what about my updateinfos. If I add them MySQL will update them every time. How can I do it that MySQL only sets the new updateinfos if there is really a changed value?

For Example

mysql SET a=1, b=2, Updateinfo='TIMESTAMP+IP'

What I looking for is a kind like this

mysql SET a=1, b=2, (Updateinfo='Timestamp+IP' ONLY IF a or b has updated)

So is there a way to add the field Updateinfo='Timestamp...' to the query if there is a change by one of the SET a=1, b=2?

Hope I could explain what I'm looking for.

you mean if a or b has new data to update than udateinfo will update? — Md. Sahadat Hossain
– Md. Sahadat Hossain, Commented Mar 14, 2017 at 5:38

Carl Binalla · Accepted Answer · 2017-03-14 08:48:34Z

1

Something like this? It's only a pseudo-code, but you get the idea

run_query("UPDATE table SET a=1, b=2");
if(success == run_query){
    run_query("UPDATE table SET Updateinfo='Timestamp+IP' WHERE a=1 AND b=2");
} else {
    //some other things...
}

Revise #1

$a = 'somevalue';
$b = 'somevalue2';

$result = fetch -> run_query('SELECT a, b FROM table');
$existingA = $result["a"];
$existingB = $result["b"];

if(($existingA != $a) || ($existingB != $b)){
    run_query("UPDATE table SET a=$a, b=$b, Updateinfo='Timestamp+IP'");
}

edited Mar 14, 2017 at 8:48

answered Mar 14, 2017 at 5:33

Carl Binalla

5,4315 gold badges31 silver badges49 bronze badges

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

9 Comments

Carl Binalla Over a year ago

Correct me if I misinterpreted OP's question

newbieRB Over a year ago

i think in this way you everytime update the updateinfo, mysql will alway sucess this if-part!?

Carl Binalla Over a year ago

I just copied what you had given, you can of course make the values dynamic.

newbieRB Over a year ago

yes of course ;-) but i think that is not the way to do what i want!?

Carl Binalla Over a year ago

If so, please edit your question so that we may understand it better. From what I understood, you will first update the a and b, then if they are successful, you will update Updateinfo, am I correct?

|

Oliver Sprites · Accepted Answer · 2022-07-21 13:25:16Z

0

here my solution in php style

UPDATE software_config SET modifiedby=IF(STRCMP(main, '$main')=0 AND STRCMP(setting, '$setting')=0 AND STRCMP(value, '$value')=0, modifiedby, $new_value), main='$main', setting='$setting', value='$value' WHERE id=$id"

answered Jul 21, 2022 at 13:25

Oliver Sprites

1

Collectives™ on Stack Overflow

Update MySQL db if value is changed and change another value

2 Answers 2

Revise #1

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Revise #1

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related