Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Issue With Updating Column From JOIN

    I'm running into an issue when trying to update a column of a table with the results of a JOIN. Here is the query:

    Code:
    UPDATE Trial_Stats_Constant_MR 
    SET
    [DF_TRUN] = (SELECT X.Trial_GUID, 
    COUNT(*)-1 --AS 'DF_TRUN'
    	FROM (SELECT T.Trial_GUID,
    		TE.Trial_Entry_GUID
    		FROM EXTT_MR AS T
    		JOIN METT_MR AS TE
    			ON T.Trial_Entry_Defaults_GUID = TE.Trial_Entry_Defaults_GUID			
    			--my additions
    			JOIN Trial_Stats_Constant_MR AS TSC
    			ON T.Trial_GUID = TSC.Trial_GUID
    Trial_Stats_Constant_MR.Trial_GUID
    			--WHERE T.Trial_GUID = TSC.Trial_GUID
    		GROUP BY T.Trial_GUID, TE.Trial_Entry_GUID) AS X
    	GROUP BY X.Trial_GUID)
    Here is the DDL for the table I'm trying to update:
    Code:
    CREATE TABLE [dbo].[Trial_Stats_Constant_MR](
    	[Trial_GUID] [uniqueidentifier] NULL,
    	[DF_MINN] [int] NULL,
    	[DF_REPN] [int] NULL,
    	[DF_TOTN] [int] NULL,
    	[DF_TRUN] [int] NULL,
    	[Entry_Count] [int] NULL,
    	[Create_Date] [datetime] NULL
    ) ON [PRIMARY]
    The Trial_GUID is unique. The query essntially performs a count of rows in the EXTT / METT table, joined by the Trial_GUID. Here's an example of the output:

    Trial_GUID (No column name)
    1809DB21-D7C5-44A7-A0A6-000017914AEC 15
    2CCF138D-CB04-47EC-9490-00003CCEB626 35
    8FDC9AC0-7711-4A72-9158-0000663BBFA0 122
    CDE2EDBA-D700-4D84-B0C9-000206728190 35
    A5AEA2E0-BD67-4FD6-8C98-0002C43A08A8 59
    A44F1ADF-589B-44CC-A28C-0003BEBF072E 93

    In the Trial_Stats_Constant table, I just want to update the DF_TRUN column with the count based on each individual Trial_GUID.

    When running the query, I get:
    Code:
    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.
    I know it's complaining about the fact that it's trying to update NENT column but there is more than one result.

    Just trying to figure out how to tell the query to match the Trial_GUID's
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    UPDATE U
    SET DF_TRUN = Y.DF_TRUN
    FROM Trial_Stats_Constant_MR AS U
    	INNER JOIN (SELECT X.Trial_GUID, COUNT(*)-1 AS DF_TRUN
    		FROM (SELECT T.Trial_GUID,
    			TE.Trial_Entry_GUID
    			FROM EXTT_MR AS T
    				INNER JOIN METT_MR AS TE ON 
    					T.Trial_Entry_Defaults_GUID = TE.Trial_Entry_Defaults_GUID			
    				--my additions
    				INNER JOIN Trial_Stats_Constant_MR AS TSC ON 
    					T.Trial_GUID = TSC.Trial_GUID
    			GROUP BY T.Trial_GUID, TE.Trial_Entry_GUID
    			) AS X
    		GROUP BY X.Trial_GUID
    		) as Y ON
    		U.Trial_GUID = Y.Trial_GUID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •