How to do updates upon the query results

Hello,

I've run a complex SELECT statement combined of the several tables, which
returns me:

MemberID MemberName CurrentWeightAvailableOn010108
NeedsToHaveWeightOnDate Modifier(NeedsToHaveWeight-CurrentWeightAvailable)
1 John Doe 100
105 5
6 Yao Ming 120
121 1
9 Adam Waters 110
106 -4

How can I run an update on tblMemberWeight, which is:

MemberWeightID MemberID Date (varchar(10))
WeightAvailable
1 1
01-01-2008 100
2 1
01-02-2008 115
3 1
01-03-2008 102
4 2
01-01-2008 121
5 2
01-02-2008 135
6 2
01-03-2008 92

If I want to update a weight on a single date for multiple members?
(Optional) If I want to update multiple dates in one go, by the same
modifier?

It's SQL2000.

The business problem is, that on the dates of measuring, wrong measures were
taken, and now we need to adjust each day by a certain modifier.

Thank you!
Mantvydas

In Books OnLine, there is an example how to update rows in one table using
information from another table:

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.SalesPersonID = so.SalesPersonID
AND so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID =
sp.SalesPersonID);
GO

Check the UPDATE (Transact-SQL) topic, I think this is what you need.

--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

"Mantvydas" <mantvydas at gmail dot com> wrote in message

> Hello,
>
> I've run a complex SELECT statement combined of the several tables, which
> returns me:
>
> MemberID MemberName CurrentWeightAvailableOn010108
> NeedsToHaveWeightOnDate
> Modifier(NeedsToHaveWeight-CurrentWeightAvailable)
> 1 John Doe 100 105
> 5
> 6 Yao Ming 120 121
> 1
> 9 Adam Waters 110
> -4
>
> How can I run an update on tblMemberWeight, which is:
>
> MemberWeightID MemberID Date (varchar(10))
> WeightAvailable
> 1 1 01-01-2008
> 100
> 2 1 01-02-2008
> 115
> 3 1 01-03-2008
> 102
> 4 2 01-01-2008
> 121
> 5 2 01-02-2008
> 135
> 6 2 01-03-2008
> 92
>
> If I want to update a weight on a single date for multiple members?
> (Optional) If I want to update multiple dates in one go, by the same
> modifier?
>
> It's SQL2000.
>
> The business problem is, that on the dates of measuring, wrong measures
> were taken, and now we need to adjust each day by a certain modifier.
>
> Thank you!
> Mantvydas