Problem with update query based on subquery

Hello all!
I have created a update query, which looks like this:
UPDATE NMR.dbo.NMR_wpisy
INNER JOIN
(SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
[Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
= [Confirmed_Production_Labor]
Could you modify these query to be right, because there is the error? I
would be very grateful for it...
Please have a look what I would achieve:
I want to sum double or more values from the table using such query
(let`s call it query1):
SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
This query works fine. After that I want to update the values of
NMR_wpisy table which are in relations with nr_NMR field from the above
query1 and there (in query1) are the values which I would like to to
update the fields. I don`t know how to do that. I was trying to do it on
MS Access and copy this whole query into the MS SQL but if the subquery
exists I cannot do that.
Please help
I would be very grateful for it
Best regards
Marcin
*** Sent via Developersdex http://www.examnotes.net ***Try,
UPDATE a
SET
a.czas_machine_sortowanie = b.[Confirmed_Production_Machine],
a.czas_labour_sortowanie = b.[Confirmed_Production_Labor]
from
NMR.dbo.NMR_wpisy as a
INNER JOIN
(
SELECT
N.nr_NMR,
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
GROUP BY
N.nr_NMR
) as b
ON b.nr_NMR = a.nr_NMR
-- or
UPDATE NMR.dbo.NMR_wpisy
SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0),
NMR.dbo.NMR_wpisy.czas_labour_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Labor])
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0)
where
exists(
select
*
from
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
)
go
AMB
"Marcin Zmyslowski" wrote:

> Hello all!
> I have created a update query, which looks like this:
> UPDATE NMR.dbo.NMR_wpisy
> INNER JOIN
> (SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
> ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
> NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
> [Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
> = [Confirmed_Production_Labor]
> Could you modify these query to be right, because there is the error? I
> would be very grateful for it...
> Please have a look what I would achieve:
> I want to sum double or more values from the table using such query
> (let`s call it query1):
> SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
> This query works fine. After that I want to update the values of
> NMR_wpisy table which are in relations with nr_NMR field from the above
> query1 and there (in query1) are the values which I would like to to
> update the fields. I don`t know how to do that. I was trying to do it on
> MS Access and copy this whole query into the MS SQL but if the subquery
> exists I cannot do that.
> Please help
> I would be very grateful for it
> Best regards
> Marcin
>
> *** Sent via Developersdex http://www.examnotes.net ***
>