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

score 4 · Accepted Answer · edited Dec 23 '14 at 22:35

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 '14 at 22:35

ᴍᴇʜᴏᴠ

4,804
4
44
57

answered Aug 11 '13 at 08:10

user4035

22,508
11
59
94

Can I send several `UPDATE` in one query? – user2465936 Aug 11 '13 at 08:12
1

@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: http://stackoverflow.com/questions/1994408/perl-update-multiple-rows-with-one-mysql-call – user4035 Aug 11 '13 at 08:15
"It won't work with compound clauses" - where is this documented? – fooquency Nov 12 '15 at 15:29
@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. – user4035 Nov 12 '15 at 18:18

score 2 · Answer 2 · answered Aug 11 '13 at 08:06

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 '13 at 08:06

aleroot

71,077
30
176
213

Updated question with `CASE`, but also error... will try to modify – user2465936 Aug 11 '13 at 08:08

PHP MySQL update with multiple WHERE (syntax error)

2 Answers2