在一个查询中更新/增加多个MySQL列

I have this query, which works...

我有这个查询，它可以…

UPDATE `contacts` 
       SET `calls_to`=`calls_to`+1 
  WHERE `contact_no` = '0412345678';

What I also want to do is add a value to the cost field. From my understanding, the way to do this would be...

我还想做的是向cost字段添加一个值。根据我的理解，这样做的方法是……

UPDATE `contacts` 
       SET `calls_to` = `calls_to`+1, 
             `cost_to` = `cost_to`+0.25 
  WHERE `contact_no`='0412345678';

Obviously, as I'm posting here, it's not working as I would expect.

显然，正如我在这里发布的，它并没有像我预期的那样发挥作用。

--UPDATE--

——更新

As requested, the table structure..

按照要求，表结构。

id                  int(255) auto_increment
contact_owner  varchar(255)
contact_no       varchar(11)
contact_name   varchar(255)
calls_to            int(255)
txts_to             int(255)
time_talked_to   int(255)
cost_to            decimal(65,2)

4 个解决方案

#1

Check if the datatype for cost_to is int or not.Also update the column if it's value is not null.

检查cost_to的数据类型是否为int类型。如果列的值不是null，也要更新它。

UPDATE `contacts` 
       SET `calls_to` = `calls_to`+1, 
             `cost_to` = `cost_to`+0.25 
  WHERE `contact_no`='0412345678' AND
          calls_to is not null AND
          cost_to is not null;

#2

On first glance the query looks fine. What is the type of the cost_to field? Double check it's not an integral type, since you will then not get the result you are looking for. (As a test, add a larger value, say, 4 to cost_to.)

乍一看，查询看起来还不错。cost_to字段的类型是什么?再次检查它不是一个整数类型，因为你将不会得到你想要的结果。(作为测试，向cost_to添加一个较大的值，比如4)。

#3

I think your calls_to field as int and cost_to field as in different type, so only you didn't got the result. Check the type of the field.

我认为您的calls_to字段作为int和cost_to字段是不同类型的，所以只有您没有得到结果。检查字段的类型。

#4

Single-table syntax:

文档的语法:

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

Multiple-table syntax:

多个表的语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value. The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

对于单表语法，UPDATE语句使用新值更新指定表中已有行的列。SET子句指示要修改的列和应该给出的值。每个值都可以作为表达式给出，或者使用关键字DEFAULT将列显式设置为其默认值。如果给定WHERE子句，则指定确定要更新哪些行的条件。没有WHERE子句，所有行都被更新。如果指定ORDER BY子句，则按照指定的顺序更新行。LIMIT子句限制可以更新的行数。

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

对于多表语法，更新更新表中每个表中指定的行，以满足条件。每个匹配行更新一次，即使它多次匹配条件。对于多表语法，不能使用ORDER BY和LIMIT。

#1