1

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

Improve this question
3
  • 1
    what does the actual syntax error look like that you are getting from mysql server? can you post? 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 Commented Jul 6, 2015 at 19:11
  • Then what will the right code sir.? Commented Jul 6, 2015 at 19:17

1 Answer 1

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

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.
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
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.
@jpw thanks for adding. I decided to answer before making the Fiddle haha. Here's mine: sqlfiddle.com/#!9/1bcd8/1
@McAdam331 Fastest wins! In any case the question is a duplicate of several other.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.