weixin_42692183
weixin_42692183
2021-01-27 11:09

MySQL执行更新脚本时报错1064

  • java
  • mysql
  • sql
  • 开发语言

MySQL执行更新脚本时报错1064 - You have an error in your SQL syntax; 

在MSSQL执行正常,在MySQL执行报错。

两个表:

card表,字段userid,CardLastSum

Businesslogs表,字段userid,cardusingtimes 、 balance、BusinessTypeID

其中userid字段在两表是一样,想把Businesslogs表最大序号(cardusingtimes)的记录的balance值,更新给card表的CardLastSum字段。

如下语句:

SET CardLastSum = (
	SELECT
		TOP 1 balance
	FROM
		Businesslogs
	WHERE
		Businesslogs.UserID = card.UserID
	AND BusinessTypeID NOT IN (1,2)
	ORDER BY
		CardUsingTimes DESC
)
WHERE
	card.UserID IN (
		SELECT DISTINCT(UserID)
		FROM
			Businesslogs
		WHERE
			BusinessTypeID NOT IN (1,2)
	)

执行后,报一个错

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 balance
    FROM
        Businesslogs
    WHERE
        Businesslogs.UserID = card.UserID
    ' at line 3

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

4条回答