UPDATE语句

单表更新
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

多表更新
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

单表：
如果指定了ORDER BY子句，则按指定的顺序更新行。 LIMIT子句限制可以更新的行数

修饰符

LOW_PRIORITY：延迟执行，直到没有其他客户端从表中读取为止。这会影响只使用表级锁只存储引擎（例如 MyISAM，MEMORY和 MERGE）。
IGNORE：即使更新期间发生错误，update语句也不会中止。在唯一键值上发生重复键冲突的行不会更新。为可能导致数据转换错误的值的行更新为最近的有效值

UPDATE返回实际更改的行数。mysql_info()函数返回被匹配和更新的行数、发生警告的数量 

不能直接在子查询中从同一表中选择更新，可以使用多表更新来完成

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);
mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

#将子查询移到要更新的表列表中，使用别名在最外层WHERE子句中引用它
UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

示例

update a
set a.InstrumentTypeID = b.DictionaryEnumID
from t_Basic_Instrument a
left join t_Basic_DictionaryEnum b on b.DictionaryEnumText=a.InstrumentName