1

Well, I would like to do this:

SELECT IF(1<2, (UPDATE `table` SET `column` = 'iam an update' WHERE `id` LIKE '12345'), 0)

but as I see it doesn't work... lame!

So, I read this post Update MySQL with if condition and this one too MySQL: update a field only if condition is met. And if I understand correctly, I can only put IF() inside the UPDATE, and so to do this:

UPDATE `table` SET `column` = IF(1<2, 'iam an update', 'iam not an update')
WHERE `id` LIKE '12345'

But this is not what I want. What I want is not to use the UPDATE at all if I don't need that. I don't know, but it seems important for me not to run "dump" queries. Why would I need this:

UPDATE `table` SET `column` = 'iam not an update' WHERE `id` LIKE '12345'

if it actually does nothing, I mean actual changes in DB? I already have 'iam not an update' value there, I do not want to reset it! But okay, maybe I don't understand how MySql works, and it so smart that doesn't reset sh..t if values are the same, somehow I doubt that.

My question is simple, is there a way to? Really? In 202.. almost 2022 there's no way to avoid unnecessary queries to DB? Sorry, if I'm kinda emotional.

P.S. And yes, maybe the title must be: Can I use UPDATE inside IF() function?

Robbie KN
  • 317
  • 1
  • 8
  • 1
    The decision to update or not is not part of an UPDATE statement. This decision needs to happen somewhere else in your code in PHP, Java, etc. – lukas.j Nov 02 '21 at 11:15
  • 1
    Alternative: use a Stored Procedure. – lukas.j Nov 02 '21 at 11:17
  • 1
    Second alternative: put the 'decision' in the WHERE clause, so that no row is selected for the update. – lukas.j Nov 02 '21 at 11:18
  • 2
    ```UPDATE `table` SET `column` = 'iam an update' WHERE `id` LIKE '12345' AND (1<2)``` – Akina Nov 02 '21 at 11:23
  • 1
    ```UPDATE `table` SET `column` = IF( `id` LIKE '12345' AND 1<2, 'iam an update', 'iam not an update')``` – Akina Nov 02 '21 at 11:24
  • 1
    *Can I use UPDATE inside IF() function?* No, this is absolutely impossible. Some DBMSs (for example, Postgre) allows UPDATE in CTE - but not MySQL. – Akina Nov 02 '21 at 11:25
  • @lukas.j I disagree with your first comment, at least partially. I mean yes, on PHP, Java, Python, etc. we can flexibly decide what to do, but this is also a thing what I was trying to avoid particularly in this case. What I'm doing (and want) is put everything in one request and shift the responsibility of making the decision to MySql, if that possible. About first part of your comment, well solutions of Akina works for me, the update is not happening where I don't need it. – Robbie KN Nov 02 '21 at 13:12
  • Silly me, of course @Akina, why didn't I even thought about that, your solutions are good for me. If you put this in an answer I'll accept it, or not, if you don't ;) – Robbie KN Nov 02 '21 at 13:12
  • And @Akina, thanks for extened note about 'Some DBMSs', especially about Postgre I just need that too – Robbie KN Nov 02 '21 at 13:15

0 Answers0