06. juli 2004 - 15:45Der er
6 kommentarer og 1 løsning
Problemer med kørsel af SQL kode opdateringsforespørgsel
Jeg skal opdatere en værdi i én tabel med en værdi fra en forespørgsel. Desværre udløser det en fejl, da der er en aggregatforespørgsel i forespørgslen. Derfor kan jeg ikke joine og har nu forsøgt, at løse det, ved at undgå at joine. Men det har heller ikke virket.
Her er SQL:
UPDATE stamoplysninger AS st SET st.[overtagelsesdato] = format(x.confirmedexecutiondate, "@@@@-@@-@@") FROM (SELECT q.orderNumber, q.datotid, c.id, c.confirmedexecutiondate, c.confirmedexecutiontime, c.transactiontypename FROM(SELECT a.orderNumber, Max(a.[sentdate] & a.[senttime]) as datotid FROM [och flows] AS a GROUP BY a.dbordernumber) AS q INNER JOIN(SELECT b.id, b.sentdate & b.senttime AS datotid, b.dbordernumber,b.confirmedexecutiontime, b.confirmedexecutiondate, d.[transaction type name] AS transactiontypename FROM [och flows] AS b INNER JOIN [transaction types] AS d ON b.transactiontype = d.[transaction type]) AS c ON q.datotid = c.datotid AND q.dbordernumber = c.dbordernumber WHERE c.transactiontypename = "NP Confirmation") AS x WHERE st.løbenummer = x.ordernumber
Jeg håber meget, at der er nogen, som kan give mig et forslag til en SQL, som virker. (Hele selectdelen virker uden problemer) Hvis jeg prøver, at køre en join mellem stamoplysninger og forespørgslen får jeg en fejl, da forespørgslen indeholder aggregatfunktioner.
! I may have missed something here, but you are trying to update ONE FIELD (st.[overtagelsesdato]) but you are selecting a number of fields! Why do you need to select q.orderNumber, q.datotid, c.id and the other fields when all you want is confirmedexecutiondate?
They´re not important, but i can see its confusing. I´ve made a simpler example: Update salespersons AS SP SET SP.latestsale = LS.sale FROM (SELECT L.salespersonID, S.sale FROM sales AS S INNER JOIN (SELECT salespersonID, MAX(sales.date & sales.time) AS latestsaleprID FROM SALES GROUP BY salespersonID) AS L ON S.salespersonID = L.salespersonID AND S.date & S.time = L.latestsaleprID) AS LS WHERE LS.salespersonID = S.salespersonID
The usual way to do this in Access would be to join the table salespersons(SP) with the subquery (LS) but a but the query (LS) can´t be updated since the field latestsaleprID consists of two fieldvalues. (So a join is not an option) I am doing this in Access. As far as i know this method should work in SQL server, but i have not had a chance to try it out.
Make a query using the code which you made yourself for finding the latest sales for each salesperson. Name the query qryLatestSales.
SELECT L.salespersonID, S.sale FROM sales AS S INNER JOIN [SELECT salespersonID, MAX(sales.date & sales.time) AS latestsaleprID FROM SALES GROUP BY salespersonID]. AS L ON (S.date & S.time = L.latestsaleprID) AND (S.salespersonID = L.salespersonID);
Now make a query for doing the actual update.
UPDATE salespersons AS SP SET SP.Latestsale = DFirst("Sale","qryLatestSales","SalespersonID = " & [SP]![SalespersonID]);
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
