SQL : Update table with select

Question

I have a query like this

UPDATE t_prd_cost_compare 
SET 
    2015_AUG_PRD_UNIT_PRICE=i.PRD_UNIT_PRICE, 
    2015_AUG_PRD_SELLING_PRICE=i.PRD_SELLING_PRICE,
    2015_AUG_PRD_IN_PATIENT_LIST_PRICE=i.PRD_IN_PATIENT_LIST_PRICE,
    2015_AUG_PRD_OUT_PATIENT_LIST_PRICE=i.PRD_OUT_PATIENT_LIST_PRICE
FROM (
    SELECT PRODUCTID,PRD_UNIT_PRICE,PRD_SELLING_PRICE,PRD_IN_PATIENT_LIST_PRICE,PRD_OUT_PATIENT_LIST_PRICE
    FROM t_product_catalog
    LEFT JOIN T_adjust ON IAJ_PRODUCTID=PRODUCTID AND IAJ_ADJNO IS NULL
    WHERE PRODUCTID>1 AND (DATE(IAJ_DATE) = '2015-01-01')
    GROUP BY IAJ_PRODUCTID
    ) AS i
WHERE i.PRODUCTID = t_prd_cost_compare.PRODUCTID

I get error like this

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 ( SELECT PRODUCTID,PRD_UNIT_PRICE,PRD_SELLING_PRICE,PRD_IN_PATIENT_LIST_PRI' at line 7

I done checked the select statement is correct, but I still get error!

Any idea?

There is no FROM in an UPDATE statement. Try using a JOIN instead. — Dan Belden, Oct 04 '15 at 05:59
A good rule of thumb: if there's no aggregation, and you don't know what you're doing, don't use GROUP BY — Strawberry, Oct 04 '15 at 07:08

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

Issue solved, here is the solution

Update
  Competition as C
  inner join (
    select CompetitionId, count(*) as NumberOfTeams
    from PicksPoints as p
    where UserCompetitionID is not NULL
    group by CompetitionID
  ) as A on C.CompetitionID = A.CompetitionID
set C.NumberOfTeams = A.NumberOfTeams

refer from: mysql update query with sub query

edited May 23 '17 at 11:44

Community

1
1

answered Oct 04 '15 at 06:10

Teddybugs

1,232
1
12
37

1

I don't think we could have guessed at *this* answer from *that* question, but we'll done for figuring it out anyway – Strawberry Oct 04 '15 at 07:11

SQL : Update table with select

1 Answers1