Database update query

Question

This is the Query which i did earlier to fetch sum

    select  E.Id as StaffId,
            SUM(IC.AmountReceived) as totalAmount,INV.InvoiceType as AccountType
            from invoice_collection IC,employee E ,invoice INV
            where IC.CollectedBy=E.id and IC.OperatorCode=#operatorCode#
            and INV.InvoiceNo=IC.InvoiceNo and E.Id=#staffId# and IC.journalFetchStatus=1
            and TxnDate > #CreatedOn#
            group by INV.InvoiceType;

I am trying to solve another update issue with the same conditions

update invoice_collection set journalFetchStatus=0
        where IC.CollectedBy=E.id and IC.OperatorCode=#operatorCode#
        and INV.InvoiceNo=IC.InvoiceNo and E.Id=#staffId#
        and TxnDate > #CreatedOn#
        group by INV.InvoiceType;

I would like add "Invoice_collection IC,Employee E.." etc ,where should i add these! — Nithin Maliackal, Jan 27 '16 at 09:27
http://stackoverflow.com/questions/9957171/how-to-join-two-tables-in-an-update-statement — slavoo, Jan 27 '16 at 09:35
you cannot use an aggregate function (group by) in update.... — Lelio Faieta, Jan 29 '16 at 10:22

score 0 · Accepted Answer · answered Jan 27 '16 at 09:36

0

Try this:

update invoice_collection IC
 inner join Employee E on IC.CollectedBy=E.id
 inner join invoice INV on INV.InvoiceNo=IC.InvoiceNo
 set journalFetchStatus=0
 where IC.OperatorCode=#operatorCode#
and E.Id=#staffId#
and TxnDate > #CreatedOn#;

answered Jan 27 '16 at 09:36

Abhishek Ginani

4,511
4
23
35

Welcome @Nithin Maliackal :) – Abhishek Ginani Jan 27 '16 at 09:58

Database update query

1 Answers1