这是有效的查询

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

我只想在一个查询中进行多个更新，比如

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

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

基于此http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/例子

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

将查询更改为此

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

但是得到错误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'

据了解，错误发生在 SET 之后.

删除了括号。对于第一个CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?没有错误，但错误接近第二 CompanyName = ?, VatCode = ?

什么是正确的语法？

也试过这个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

但是得到错误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 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

所以对于每个 UPDATE只有一个SET RegistrationNumber = CASE但可以设置多个条件WHEN CompanyName = 1 OR VatCode = 1 .

接下来要么尝试组合多个UPDATE 18_6_TransactionPartners SET RegistrationNumber = CASE UPDATE 18_6_TransactionPartners SET RegistrationNumberNEXT = CASE

或者需要创建多个查询....似乎需要尝试这个例子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

最佳答案

不，这是不正确的，不会起作用。作为manual说，UPDATE 具有以下语法:

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

请求中只能有 1 个 WHERE 子句。实际上，您的博客页面明确指出:“您不能像这样在单个 UPDATE 查询中对表进行多项更改”。

他正在使用 CASE 命令:

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

但 CASE 仅适用于 1 个变量:

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

它不适用于像你这样的复合子句:

CompanyName = ? OR RegistrationNumber = ?

因此，您必须发送多个更新请求。 EDIT 从 CASE 条件中删除了分号

关于mysql - 具有多个 WHERE 的 PHP MySQL 更新(语法错误)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18169946/