March 22, 2012 at 1:46 am
Hi,
I have two tables. Table 1 for Head of Family Details and Table 2 for Dependents Details. FamilyID column in Table1 is Primary Key and is Foreign Key in Table2.
Now I had inserted a new column in Table1 as "TotalDependets" and tried to run this query
UPDATE dbo.tblHOF
SET dbo.tblHOF.TotalDependents = (SELECT count(*) FROM dbo.tblDependents as a inner join dbo.tblHOF as b on a.FamilyID = b.FamilyID group by a.FamilyID)
In this query, as the subquery(select count(*) ....) returns more than one row, am getting the following error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Please guide me on on this...
March 22, 2012 at 2:19 am
How many results are returned by this statement?
SELECT count(*) FROM dbo.tblDependents as a inner join dbo.tblHOF as b on a.FamilyID = b.FamilyID group by a.FamilyID
your group by clause could return more than one row from the query, since we do not have sample data of your table and data, I assume this is the case.
Your are trying to update a column with multiple row data.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 22, 2012 at 2:45 am
I want only one row to return, to get update to "dbo.tblHOF.TotalDependents" column.
Snapshot of both tables is as follows:
Table 1: tblHOF
FamilyID HoFName Age Gender DoorNo TotalDependents
1001 aaa1 47 M 1-A
1002 bbb1 38 M 1-B
1003 ccc1 46 F 1-C
Table 2: tblDependents
FamilyID MemberID Name Age Gender RelationType
1001 1 aaa1 47 M Self
1001 2 aaa2 38 F Spouse
1001 3 aaa3 20 M Son
1001 4 aaa4 17 M Son
1002 1 bbb1 38 M Self
1002 2 bbb2 35 F Spouse
1002 3 bbb3 68 M Father
1003 1 ccc1 46 F Self
1003 2 ccc2 24 F Daughter
1003 3 ccc3 20 M Son
Now after updating the query, for FamilyI '1001', total dependents will be 4, for '1002' & '1003', its 3.
March 22, 2012 at 3:30 am
Try it as a correlated subquery and lose the GROUP BY, thusly:
DECLARE @HOF TABLE (
FamilyID INT, HoFName CHAR(4), Age INT, Gender CHAR(1), DoorNo CHAR(3), TotalDependents INT)
DECLARE @Dep TABLE (
FamilyID INT, MemberID INT, [Name] CHAR(4), Age INT, Gender CHAR(1), RelationType VARCHAR(10))
INSERT INTO @HOF (FamilyID, HoFName, Age, Gender, DoorNo)
SELECT 1001 AS FamilyID, 'aaa1' AS HoFName, 47 As Age, 'M' As Gender, '1-A' As DoorNo
UNION ALL SELECT 1002, 'bbb1', 38, 'M', '1-B'
UNION ALL SELECT 1003, 'ccc1', 46, 'F', '1-C'
INSERT INTO @Dep (FamilyID, MemberID, [Name], Age, Gender, RelationType )
SELECT 1001 AS FamilyID, 1 AS MemberID, 'aaa1' AS [Name], 47 AS Age, 'M' AS Gender, 'Self' AS RelationType
UNION ALL SELECT 1001, 2, 'aaa2', 38, 'F', 'Spouse'
UNION ALL SELECT 1001, 3, 'aaa3', 20, 'M', 'Son'
UNION ALL SELECT 1001, 4, 'aaa4', 17, 'M', 'Son'
UNION ALL SELECT 1002, 1, 'bbb1', 38, 'M', 'Self'
UNION ALL SELECT 1002, 2, 'bbb2', 35, 'F', 'Spouse'
UNION ALL SELECT 1002, 3, 'bbb3', 68, 'M', 'Father'
UNION ALL SELECT 1003, 1, 'ccc1', 46, 'F', 'Self'
UNION ALL SELECT 1003, 2, 'ccc2', 24, 'F', 'Daughter'
UNION ALL SELECT 1003, 3, 'ccc3', 20, 'M', 'Son'
UPDATE h
SET TotalDependents = (
SELECT COUNT(*)
FROM @Dep a
WHERE a.FamilyID = h.FamilyID )
FROM @HOF h
SELECT * FROM @HOF
Has anyone mentioned that adding this dependents column is bad normalization practice?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 22, 2012 at 4:31 am
Thank you. I got it. Dependents details are in a separate table and we need total dependents details in a another table, which is in a separate server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply