我正在使用以下格式在同一查询中使用select
和update
命令。
UPDATE t
SET t.col1 = o.col1
FROM table1 AS t
INNER JOIN
table2 AS o
ON t.id = o.id
我在查询中应用了相同的概念,但是它抛出了一个我无法解决的错误。知道我在哪里错了吗?
update T set T.price = 2*OT.ingredients from Cake as T Inner join (select
A.cakeid, B.price, sum(C.price) ingredients
from
Contain as A
inner join
Cake as B ON A.cakeid = B.cakeid
inner join
Ingredient as C ON C.ingredid = A.ingredid
group by A.cakeid
having B.price <= 2 * sum(C.price) ) as OT on OT.cakeid = T.cakeid
Error Code: 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 'from Cake as T Inner join (select A.cakeid, B.price, sum(C.price) ingredien' at line 1
最佳答案
MySQL中正确的语法是:
update Cake T Inner join
(select A.cakeid, B.price, sum(C.price) ingredients
from Contain A inner join
Cake B
ON A.cakeid = B.cakeid inner join
Ingredient as C
ON C.ingredid = A.ingredid
group by A.cakeid
having B.price <= 2 * sum(C.price)
) OT
on OT.cakeid = T.cakeid
set T.price = 2*OT.ingredients ;