PHP MySQL update with multiple WHERE (syntax error)

Question

Here is query that works

UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
WHERE CompanyName = ? OR RegistrationNumber = ?

I just want in one query make multiple updates, like

UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
WHERE CompanyName = ? OR RegistrationNumber = ?

UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
WHERE CompanyName = ? OR RegistrationNumber = ?

Based on this http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/ example

UPDATE mytable
SET (title='Great Expectations' WHERE id='1'),
(title='War and Peace' WHERE id='2');

Changed query to this

UPDATE 18_6_TransactionPartners 
SET (CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?), 
(CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?)

But get error Syntax error or access violation: 1064 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 '(CompanyName = 'first name', VatCode = '123' WHERE CompanyName = 'first name' OR'

As understand, error is after SET.

Removed brackets. For first CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ? no error, but error is near second CompanyName = ?, VatCode = ?

What is correct syntax?

Tried also this https://stackoverflow.com/a/15344247/2465936

UPDATE 18_6_TransactionPartners 
SET value = CASE 
WHEN CompanyName = ? OR RegistrationNumber = ? THEN CompanyName = ?, VatCode = ? , 
WHEN CompanyName = ? OR RegistrationNumber = ? THEN CompanyName = ?, VatCode = ? 
ELSE CompanyName, RegistrationNumber END

But get error SQLSTATE[42000]: Syntax error or access violation: 1064 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 ' VatCode = '123' , WHEN CompanyName = 'second name' OR RegistrationNumber = 'ttt'

Update

Experimented and works such code:

UPDATE 18_6_TransactionPartners SET RegistrationNumber = (
CASE
WHEN CompanyName = 1 OR VatCode = 1 THEN 1
WHEN CompanyName = 2 OR VatCode = 2 THEN 2
WHEN CompanyName = 3 OR VatCode = 'three' THEN 'three'
ELSE RegistrationNumber
END)
WHERE CompanyName IN ('1', '2', '3') OR VatCode IN ( 1, 2, 'three' ) //aim is to search table only where `WHERE` is true

So for each UPDATE only one SET RegistrationNumber = CASE but can set multiple conditions WHEN CompanyName = 1 OR VatCode = 1.

Next either to try to combine multiple UPDATE 18_6_TransactionPartners SET RegistrationNumber = CASE UPDATE 18_6_TransactionPartners SET RegistrationNumberNEXT = CASE

or need to create multiple queries.... Seems need to try this example http://code.openark.org/blog/mysql/multi-condition-update-query

UPDATE
 film
SET
 rental_duration=IF(rating = 'G', rental_duration+1, rental_duration),
 rental_rate=IF(length < 90, rental_rate-0.5, rental_rate)
WHERE
 rating = 'G'
 OR length < 90

ᴍᴇʜᴏᴠ · Accepted Answer · 2014-12-23 22:35:55Z

4

No, this is incorrect and won't work. As manual says, UPDATE has the following syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

There can be only 1 WHERE clause in the request. Actually, your blog page clearly says: "you can’t do multiple changes to a table in a single UPDATE query like this".

He is using CASE command:

UPDATE mytable SET title = CASE
    WHEN id = 1 THEN 'Great Expectations'
    WHEN id = 2 THEN 'War and Peace'
ELSE title
END;

But CASE will work for only 1 variable:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

It won't work with compound clauses like yours:

CompanyName = ? OR RegistrationNumber = ?

So, you'll have to send several UPDATE requests. EDIT removed semi colons from the CASE conditions

edited Dec 23, 2014 at 22:35

ᴍᴇʜᴏᴠ

5,3494 gold badges48 silver badges61 bronze badges

answered Aug 11, 2013 at 8:10

user4035

24k11 gold badges69 silver badges101 bronze badges

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

4 Comments

user2465936 Over a year ago

Can I send several UPDATE in one query?

user4035 Over a year ago

@user2465936 Yes, the queries must be separated by a semicolon. But not all the SQL drivers support it. Here is a useful answer for you: stackoverflow.com/questions/1994408/…

fooquency Over a year ago

"It won't work with compound clauses" - where is this documented?

user4035 Over a year ago

@fooquency Actually, there are several types of CASE WHEN statements. And one of them supports expressions. So, it's possible to use expressions inside CASE statements.

aleroot · Accepted Answer · 2013-08-11 08:06:25Z

2

You need a CASE :

UPDATE mytable
SET title= (CASE WHEN id='1' THEN 'Great Expectations' ELSE 'War and Peace' END) 
WHERE id IN ('1','2');

answered Aug 11, 2013 at 8:06

aleroot

73k32 gold badges182 silver badges220 bronze badges

1 Comment

user2465936 Over a year ago

Updated question with CASE, but also error... will try to modify

Collectives™ on Stack Overflow

PHP MySQL update with multiple WHERE (syntax error)

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related