Syntax error when using aggregation in update query [duplicate]

Question

There are 2 tables.(Invoice,Installments). First one is basic details of an invoice.

Invoice table: --------- Invoice_Num Value Down_Payment Balance Customer_name Item_ID Last_Paid_date

and etc.

Second is installments of the invoice.

Installment table: --------- Invoice_Num Date Paid Balance

ex : Some one bought a laptop on 2015-June-25. Total price 1000$ . He paid 200 $ as down payment. Balance was 800 $ . Then he paid 5 installment like this

Date Paid Balance 2015-July-25 100$ 700$ 2015-Aug-25 100$ 600$ 2015-Sep-10 150$ 450$ 2015-Oct-18 100$ 350$ 2015-Nov-23 120$ 230$

Now I want to update 2015-Nov-23 in first table column name "Last_Paid_date"

I tried this.

update invoice,installment set invoice.last_paid_date=max(date) where invoice.invoice_num=installment.invoice_num group by installment.num

There was Syntax error:

java.sql.SQLException: 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 'group by installment.card' at line 1

what does the actual syntax error look like that you are getting from mysql server? can you post? — BK435
– BK435, Commented Jul 6, 2015 at 19:05
Yes sir. here is it. java.sql.SQLException: 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 'group by installment.card' at line 1 — Hasan Dulanga
– Hasan Dulanga, Commented Jul 6, 2015 at 19:11

AdamMc331 · Accepted Answer · 2015-07-06 19:16:09Z

5

You cannot use group by in an update statement. What you should do here is write a query to get the max date in each group, and use a JOIN in the update to set the right values:

UPDATE invoice i JOIN( SELECT invoice_num, MAX(datecolumn) AS latestDate FROM installment GROUP BY invoice_num) tmp ON tmp.invoice_num = i.invoice_num SET i.last_paid_date = tmp.latestDate;

answered Jul 6, 2015 at 19:16

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

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

11 Comments

AdamMc331 Over a year ago

This is untested, as I don't have any sample data to create an SQL Fiddle with. If you are unable to get this to work, let me know.

BK435 Over a year ago

Even if it is untested, you pointed out the error in his update statement and provided him with enough of an answer to move forward...+1

jpw Over a year ago

I was about to post the same answer but wasted some time making a fiddle instead... sqlfiddle.com/#!9/d17d46/1 tested and works as expected.

AdamMc331 Over a year ago

@jpw thanks for adding. I decided to answer before making the Fiddle haha. Here's mine: sqlfiddle.com/#!9/1bcd8/1

jpw Over a year ago

@McAdam331 Fastest wins! In any case the question is a duplicate of several other.

|

Collectives™ on Stack Overflow

Syntax error when using aggregation in update query [duplicate]

1 Answer 1

11 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Linked

Related