Update and select query in mysql query

Question

I am using the below format to use select and update command in the same query.

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

I applied the same concept in my query but it is throwing an error which I am not able resolve. Any idea where I am going wrong here?

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

The first query is not correct syntax for MySQL.

Gordon Linoff
– Gordon Linoff

2015-10-18 16:13:07 +00:00
Commented Oct 18, 2015 at 16:13 — Gordon Linoff
– Gordon Linoff, Commented Oct 18, 2015 at 16:13

Gordon Linoff · Accepted Answer · 2015-10-18 16:15:01Z

1

The correct syntax in MySQL is:

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 ;

answered Oct 18, 2015 at 16:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update and select query in mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related