Try this:
Update MasterTbl Set
TotalX = Sum(D.X),
TotalY = Sum(D.Y),
TotalZ = Sum(D.Z)
From MasterTbl M Join DetailTbl D
On D.MasterID = M.MasterID
Depending on which database you are using, if that doesn't work, then try this (this is non-standard SQL but legal in SQL Server):
Update M Set
TotalX = Sum(D.X),
TotalY = Sum(D.Y),
TotalZ = Sum(D.Z)
From MasterTbl M Join DetailTbl D
On D.MasterID = M.MasterID
As mentioned in comments, if your database software does not allow the use of From clauses in Updates, then you must use the subquery approach mentioned in several other answers
Answer from Charles Bretana on Stack OverflowTry this:
Update MasterTbl Set
TotalX = Sum(D.X),
TotalY = Sum(D.Y),
TotalZ = Sum(D.Z)
From MasterTbl M Join DetailTbl D
On D.MasterID = M.MasterID
Depending on which database you are using, if that doesn't work, then try this (this is non-standard SQL but legal in SQL Server):
Update M Set
TotalX = Sum(D.X),
TotalY = Sum(D.Y),
TotalZ = Sum(D.Z)
From MasterTbl M Join DetailTbl D
On D.MasterID = M.MasterID
As mentioned in comments, if your database software does not allow the use of From clauses in Updates, then you must use the subquery approach mentioned in several other answers
Why are you doing a group by on an update statement? Are you sure that's not the part that's causing the query to fail? Try this:
update
MasterTbl
set
TotalX = Sum(DetailTbl.X),
TotalY = Sum(DetailTbl.Y),
TotalZ = Sum(DetailTbl.Z)
from
DetailTbl
where
DetailTbl.MasterID = MasterID
Videos
You need a table variable:
declare @values table
(
Value varchar(1000)
)
insert into @values values ('A')
insert into @values values ('B')
insert into @values values ('C')
select blah
from foo
where myField in (select value from @values)
Ideally you shouldn't be splitting strings in T-SQL at all.
Barring that change, on older versions before SQL Server 2016, create a split function:
CREATE FUNCTION dbo.SplitStrings
(
@List nvarchar(max),
@Delimiter nvarchar(2)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( WITH x(x) AS
(
SELECT CONVERT(xml, N'<root><i>'
+ REPLACE(@List, @Delimiter, N'</i><i>')
+ N'</i></root>')
)
SELECT Item = LTRIM(RTRIM(i.i.value(N'.',N'nvarchar(max)')))
FROM x CROSS APPLY x.nodes(N'//root/i') AS i(i)
);
GO
Now you can say:
DECLARE @Values varchar(1000);
SET @Values = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN dbo.SplitStrings(@Values, ',') AS s
ON s.Item = foo.myField;
On SQL Server 2016 or above (or Azure SQL Database), it is much simpler and more efficient, however you do have to manually apply LTRIM() to take away any leading spaces:
DECLARE @Values varchar(1000) = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN STRING_SPLIT(@Values, ',') AS s
ON LTRIM(s.value) = foo.myField;
As mentioned in the comment, it completely depends on what you plan to do with the parameter as to the best method to accomplish this (and perhaps to add even how you get the selected values to be stored). If you can elaborate on those 2 things, we can probably give you better suggestions.
One thing you can easily use to store multiple values without having to get into parsing those values back out is to use table variables:
DECLARE @Params TABLE (A NVARCHAR(MAX))
INSERT @Params VALUES
('ABC'), ('DEF'), ('GHI'), ('JKL')
SELECT * FROM @Params
If you must iterate over the list, you can modify this to add an identity to simplify the logic.
DECLARE @Params TABLE (A NVARCHAR(MAX), Id INT NOT NULL IDENTITY(1,1))
Now you can iterate with logic like this:
DECLARE @i INT = 0
WHILE EXISTS (SELECT * FROM @Params P WHERE Id >= @i) BEGIN
SELECT * FROM @Params P WHERE Id = @i
SET @i = @i + 1
END
Or for scenarios where you only wish to process each unique value once, you can just use a DELETE (no need for additional identity attribute):
DECLARE @A NVARCHAR(MAX)
WHILE EXISTS (SELECT * FROM @Params P) BEGIN
SET @A = (SELECT TOP 1 A FROM FROM @Params P)
-- Perform any work
DELETE @Params WHERE A = @A
END
I don't know if this is going to help but... there is a type called cursor, it's like a vector or an array. It's used like this:
CURSOR name IS SELECT something
FROM some_table;
That way you can store a lot of information in a cursor called "name". To iterate through its values, you can use a loop like this:
OPEN name;
LOOP
FETCH name INTO x;
EXIT WHEN name%NOTFOUND;
dbms_output.put_line(x);
END LOOP;
CLOSE name;
This way you print all values stored. Hope that helped!
You can use a table variable for this:
DECLARE @Table TABLE (one varchar(100), two varchar(100)
INSERT INTO @TABLE
SELECT one, two from dummytable
Like other variables, this won't be accessible from outside the scope of the proc.
It's also not good for large numbers of rows since the optimizer always assumes a single row for execution plans.
SELECT @Value1 = One, @Value2 = two FROM dummyTable;
You don't have any arrays in SQL Server.