Mysql Update 语句语法

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]


其中： LOW_PRIORITY表示更新操作在所有对这个表的读取，使用的操作结束以后才进行
IGNORE：表示如果更新导致表中出现key值的重复，不会终止更新并报错，而是不更新该行来避免重复。并且如果一个更新导致了类型转换的错误，更新也不会终止，取而代之的是
更新为最近的一种类型。

order by 是在更新前对表进行排序，为了避免意外的重复错误，例如：（懒得打字引用mysql 文档原文
      If an UPDATE statement includes an      ORDER BY clause, the rows are updated in the      order specified by the clause. This can be useful in certain      situations that might 
otherwise result in an error. Suppose that a      table t contains a column id      that has a unique index. The following statement could fail with a 
duplicate-key error, depending on the order in which rows are      updated: 

UPDATE t SET id = id + 1;
 
      For example, if the table contains 1 and 2 in the      id column and 1 is updated to 2 before 2 is      updated to 3, an error occurs. To avoid this problem, add an      
ORDER BY clause to cause the rows with larger      id values to be updated before those with      smaller values:    
  
  
 
   
   

    
    
   
   
  
  
UPDATE t SET id = id + 1 ORDER BY id DESC;


note:多表更新不能使用order by 跟limite语句