UPDATE QUERY

[amacfarl]

Hi,
Once again SQL is driving me up the wall ;-)

Scenario: Two tables,
T_Sellers - Listing all the seller information
T_Sales - Listing all sales by product.

I am wanting to update a field in T_Sellers which is the total number of sales for that seller (NumOfSales). I have written this update query:

UPDATE T_Sellers SL
SET NumOfSales = (SELECT Count(*)
FROM T_Sales SA
WHERE SA.Seller = SL.ROW_ID)

I am getting the following error message:
"Operation must use an updateable query"

I dont understand what is happening... this should work!

Thanks in advance for your help!!

Regards
Angus

 

[IFELSETHEN]

You are close. First you need to fix Count(*) to a real field name. Second you need a WHERE clause in your UPDATE statement.

Something like this …..
UPDATE T-Sellers SL
SET NumOfSales = (SELECT Count(SA.anyField)
FROM T_Sales SA
WHERE SA.Seller = SL.ROW_ID)
WHERE SA.Seller = SL.ROW_ID

John


*********************
John Nyhart

http://www.nyhart.com

*********************

 

[amacfarl]

THANKS!!