asked on

Error in Update using CASE

I get an error when executing the following code. Can someone tell me where I am going wrong?

UPDATE IC
	SET IC.InvoiceSiteID = (CASE WHEN IC.SiteID IN(SELECT InvoiceToSite FROM QuoteInvoicing 
								WHERE QuoteID = IC.QuoteID)
								THEN InvoiceToSite
							ELSE MIN(InvoiceToSite) FROM QuoteInvoicing 
								WHERE QuoteID = IC.QuoteID
									AND InvoiceToSite <> IC.SiteID 
							END)
FROM #InvoiceConsolidation IC
INNER JOIN QuoteInvoicing IQ ON IC.QuoteID = IQ.QuoteID

Open in new window

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

this should do:

UPDATE IC
      SET IC.InvoiceSiteID = (CASE WHEN IC.SiteID IN(SELECT InvoiceToSite FROM QuoteInvoicing 
                                                WHERE QuoteID = IC.QuoteID)
                                                THEN InvoiceToSite
                                          ELSE SELECT MIN(InvoiceToSite) FROM QuoteInvoicing 
                                                WHERE QuoteID = IC.QuoteID
                                                  AND InvoiceToSite <> IC.SiteID 
                                          END)
FROM #InvoiceConsolidation IC
INNER JOIN QuoteInvoicing IQ ON IC.QuoteID = IQ.QuoteID

Open in new window

Aneesh

or like this

UPDATE IC
SET IC.InvoiceSiteID = (CASE WHEN EXISTS (SELECT null FROM QuoteInvoicing
WHERE QuoteID = IC.QuoteID AND IC.SiteID = InvoiceToSite)
THEN InvoiceToSite
ELSE (SELECT MIN(InvoiceToSite) FROM QuoteInvoicing
WHERE QuoteID = IC.QuoteID
AND InvoiceToSite <> IC.SiteID )
END)
FROM #InvoiceConsolidation IC
INNER JOIN QuoteInvoicing IQ ON IC.QuoteID = IQ.QuoteID

BrookK

ASKER

Thank you All.

There was a syntax error and I have corrected it as per Aneeshattingal's first post but had to use CAST for MIN(InvoiceToSite) to convert to INT.

Thanks all
-B

Anthony Perkins

Great! Please close the question.