Menu
I have the following CTE that will give me the DocTotal for the entire invoice.
;WITH CTE_DocTotal AS ( SELECT SUM(Sale + VAT) AS DocTotal FROM PEDI_InvoiceDetail GROUP BY InvoiceNumber ) UPDATE PEDI_InvoiceDetail SET DocTotal = CTE_DocTotal.DocTotal Now with this result I want to enter into the column the DocTotal value inside PEDI_InvoiceDetail.
I know is not going to work and I know I am missing something, what is it?
981
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.
You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins. Later, query CTE for inserting data into the target table.
Updates you make to the CTE will be cascaded to the source table.
I have had to guess at your schema slightly, but something like this should work.
;WITH T AS ( SELECT InvoiceNumber, DocTotal, SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal FROM PEDI_InvoiceDetail ) UPDATE T SET DocTotal = NewDocTotal If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
