mysql和sql server的update语句的区别

摘抄自：https://blog.csdn.net/jiankunking/article/details/52626517

MySql:

UPDATE TableA
INNER JOIN tableB ON TableA.ITEMNO = tableB.ITEMNO
SET TableA.column1 = tableB.column1,
 TableA.STATUS = IFNULL(
    tableB.ITEMSTATUS,
    'ITEMSTATUS'
)
WHERE
    ID = 'ID'
AND IFNULL(TableA.ITEMNO, '') <> ''
AND IFNULL(TableA.column1, '') = ''

 

Sql Server：

UPDATE TableA
SET TableA.column1 = tableB.column1,
 TableA. STATUS = ISNULL(
    tableB.ITEMSTATUS ,'ITEMSTATUS'
)
FROM
    TableA
INNER JOIN tableB ON TableA.ITEMNO = tableB.ITEMNO
WHERE
    ID ='ID'
AND ISNULL(TableA.ITEMNO, '') <> ''
AND ISNULL(TableA.column1, '') = ''

 

 

下面是MySql更新语法
1、单表的UPDATE语句

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

 

2、多表的UPDATE语句

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

其中：server sql在Update的时候可以写成

update TableName set field='',... from TableName where field='' and ...

 

　　   mysql 中不允许写from TableName

　　　　mysql写法

update TableName set field='',...  where field='' and ...