Mysql UPDATE WIth Multiple Where statements

Question

I made a script that auto creates Update statement using For loops and all and the final result executes this:

mysql_query("UPDATE table2 
              SET(code2='sfvv' WHERE number='1'),
                 (code2='sdvsdv' WHERE number='2') 
              WHERE id='32'") or die ( mysql_error() );

The error said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(code2='sfvv' WHERE number='1'),(code2='sdvsdv' WHERE number='2') WHERE i' at line 1

Pரதீப் · Accepted Answer · 2015-08-02 13:08:20Z

2

I guess you need case statement

UPDATE table2 
SET code2= case number when 1 then 'sfvv' when 2 then 'sdvsdv' END
where id = 32

answered Aug 2, 2015 at 13:08

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

1 Comment

PHP Nexter Over a year ago

It worked for my case I finally got it working , thankyou , I learned something new :)

Anonymous · Accepted Answer · 2015-08-02 13:21:03Z

2

You use AND and OR statements to combine conditions:

UPDATE table2
SET code2 = 'sfvv'
WHERE
    (number = '1'AND code2 = 'sdvsdv')
    OR
    (number = '2' AND id = '32')

You can update multiple values in the same row by using commas to separate the statements:

UPDATE table2
SET code2 = 'sfvv', code3 = 'abc'

If you need to update different rows based on different conditions, you need to execute multiple queries:

UPDATE table2 SET code2 = 'sfvv' WHERE id = 1;
UPDATE table2 SET code3 = 'abc' WHERE id = 2;

As the other answer mentioned, you can also use case statements in your SET clause:

UPDATE table2
SET code2 = CASE WHEN number = 1 THEN 'foo' ELSE 'bar' END

But this is not 100% flexible. Use it when it makes sense.

edited Aug 2, 2015 at 13:21

answered Aug 2, 2015 at 13:06

Anonymous

12.1k3 gold badges44 silver badges51 bronze badges

4 Comments

RiggsFolly Over a year ago

You use AND and OR statements to combine conditions: Possibly you should use an OR in your first sql statement as currently that wont do anything unless number can be 1 AND 2 at the same time???

PHP Nexter Over a year ago

Number 1 and 2 are different rows and they are 1,2 they are updated to the given code2 which both are null

Anonymous Over a year ago

@RiggsFolly Thanks, was too focused on formatting the code.

PHP Nexter Over a year ago

ThankYou Both For Trying It Reminded me that I can still use OR & AND in situations like these ;)

phantom · Accepted Answer · 2015-08-02 13:33:42Z

1

Update  table2 
     SET code2= CASE 
                 WHEN  number=1 THEN 'sfvv'
                 WHEN  number=2 THEN 'sdvsdv'
                 ELSE  code2
                 END 
     WHERE id = 32

Hope it helps

answered Aug 2, 2015 at 13:33

phantom

438 bronze badges

Collectives™ on Stack Overflow

Mysql UPDATE WIth Multiple Where statements

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related