sql - 在更新语句中具有选择子查询的嵌套 SQL 查询

Question

在执行此查询时，WHERE 子句附近出现错误。

update AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ([AssetNumber] like'%" + WA_number.Text + "%') 
and ID IN (SELECT ID FROM AssetData ORDER BY ID DESC
where ([AssetNumber] like'%" + WA_number.Text + "%') LIMIT 1)

谁能帮我弄清楚这是怎么回事？

Answer 1

and ID IN (SELECT ID FROM AssetData ORDER BY ID DESC
where ([AssetNumber] like'%" + WA_number.Text + "%') LIMIT 1)

where 应该在 ORDER BY 之前。 in ( ... limit 1) 虽然合法，但没有意义，因为 in 应该与列表一起使用。我建议改用 = max(ID)

and ID = (SELECT max(ID) FROM AssetData where [AssetNumber] like'%" + WA_number.Text + "%')

您可以省略 where 子句的第一部分，因为 ID 已经符合相同的条件

update AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ID = (SELECT max(ID) FROM AssetData where [AssetNumber] like'%" + WA_number.Text + "%');