SQL - Double UPDATE with double Where

Asked

Viewed 731 times

5

Next, it is possible to make two update-where together in the same consultation? I will explain, what I wanted to do is the following:

UPDATE usuarios
   SET nome = "Matheus Silva"
 WHERE id = 1
   AND UPDATE usuarios SET nome = "Lucas Silva"
 WHERE id = 2;

But unfortunately it doesn’t work, someone knows the right way to do it?

mysql sql
  • 1

    Do not join 2 updates this way. Separate the updates in different querys.

  • But there is no way to join two updates in just one query no?

  • Have a look at http://stackoverflow.com/questions/5178169/sql-multiple-sets-in-one-update

  • I took a look at the link but unfortunately it does not solve my problem :(

2 answers

6


Possible is, but the syntax is terrible and I wouldn’t recommend it :

UPDATE usuarios SET nome = (
    CASE id WHEN 1 THEN 'Matheus Silva'
            WHEN 2 THEN 'Lucas Silva'
    END)
WHERE
    id IN (1,2);

Imagine managing this kind of query.
The ideal would be to querys separated by ; like the @Hoppy replied. 

$query = "
    UPDATE usuarios SET nome='Matheus Silva' WHERE id=1;
    UPDATE usuarios SET nome='Lucas Silva' WHERE id=2;
";
$mysqli->multi_query($query);

OBS

Mysql has a specific option for this type of execution which is the multi_query, which allows the execution of several querys in the same string, if the traditional is used query only the first will be executed.
Tip @Bacco

Or send two processes to the bank.

$query = "UPDATE usuarios SET nome='Matheus Silva' WHERE id=1;"
$this->execute($query);
$query = "UPDATE usuarios SET nome='Lucas Silva' WHERE id=2;";
$this->execute($query);

  • +1, especially for the initial example. If you want to increment, you can warn that 2 statements in the same query only work if you enable Multiple statements before (by default only runs the first one). So the first example with execute is complete. mysqli has a special treatment for this, there is no need to enable any flag: http://php.net/manual/mysqli.quickstart.multiple-statement.php

  • Thanks guys, Well I decided to choose to do the traditional way that is two same rsrs queries.

  • @Bacco, I’m reading about it, here by default it’s already so I have to better understand to complement :D

  • @Guilhermelautert quiet, I just wanted to mention it to avoid "side effects" if someone’s going to test it. This is a security measure that has been adopted to prevent further damage in the case of a successful Injection. Even if a 2nd query is injected, it will not be executed by default. If the user changes the flag of Multiple statements, it opens the possibility of attack (unless the flag changes). On the other hand the mysqli Multiple only when really necessary the attack surface decreases significantly. Note: not always this protection is active, depends on version and configuration.

  • @Bacco I thought about it while reading, really using only query, would be ideal.

  • 1

    @Guilhermelautert great answer, I did not intend to use case, I consider it a violent gambiarra. + 1

Show 1 more comment

3

$sql = "UPDATE usuarios SET nome="Matheus Silva" WHERE id=1; UPDATE usuarios SET nome="Lucas Silva" WHERE id=2;";

I don’t know what language you’re using.. but try this way, separating queries with ;

  • From what I understand, it needs to be an update query only, has the Where...in enclosure to do something similar.

  • Unfortunately I’ve tried this way but also does not work, I needed to do two update with two wheres... I’m starting to think that it is not possible to do this in the same query :(

  • 1

    Got it, Thanks for the attention and willingness to help from you, In a way already contributed enough.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.