SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error perform an Aggregate in an UPDATE statement (MS SQL)

    Hi all,

    I am getting an error message when attempting to perform an Aggregate in the SET clause of an UPDATE statement. What I want to do is to update a column in one table with the SUM of two columns from another table - why is this not possible? (I am using MS SQL Server)

    My query is as follows:

    Code:
    UPDATE pts_patient_tran
    SET pts_escorts = SUM(CAST(meridian_test.dbo.escin AS INT) + CAST(meridian_test.dbo.escoutype AS INT))
    WHERE pts_fk_master = meridian_test.dbo.planpatients
    Thanks

    Tryst
    Last edited by Tryst; Jan 24, 2005 at 03:08. Reason: Included the fact that I am using MS SQL

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You need to use a scalar subquery

    Code:
    UPDATE pts_patient_tran
    SET pts_escorts = (select
    SUM(CAST(escin AS INT) + 
        CAST(escoutype AS INT))
       from t 
    WHERE pts_fk_master = t.planpatients)
    where pts_fk_master in (select planpatients from t)
    It's not clear from your example what the name of the second table is, hence the use of t.

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, my mistake. I was missing the second table from teh query.

    The problem was that I was using the SUM statement when I didn't need to. all I need to do was to simple add (+) the two columnd together without using the SUM command (as this is used for grouping).

    Thanks

    Tryst

  4. #4
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, after working around that little problem, I am now confronted with another problem.

    I am getting the following error message when attempting to run the query...

    The column prefix 'meridian_test.dbo.planpatients' does not match with a table name or alias name used in the query.
    and my query looks like this...

    Code:
    UPDATE pts_patient_tran
    SET pts_escorts = (SELECT CAST(escin AS INT) + CAST(escintype AS INT) FROM meridian_test.dbo.planpatients)
    WHERE pts_fk_master = meridian_test.dbo.planpatients.id
    AND pts_direction = 1
    Tryst

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    If you only reference the columns directly you can use a statement like

    Code:
    UPDATE pts_patient_tran
    SET pts_escorts = CAST(escin AS INT) + CAST(escintype AS INT) 
    FROM meridian_test.dbo.planpatients, pts_patient_tran
    WHERE pts_patient_tran.pts_fk_master = meridian_test.dbo.planpatients.id
    AND pts_direction = 1
    The above syntax is specific to MSSQL server and Sybase and will not work in other DBMSs.

  6. #6
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks, swampboogie

    my final query ended up looking like this.

    Code:
    UPDATE pts_patient_tran
    SET pts_escorts = 
    	ISNULL((SELECT ISNULL(escin, 0) + ISNULL(escintype, 0)
    	FROM meridian_test.dbo.planpatients 
    	WHERE (id = pts_fk_master)), 0)
    WHERE (pts_direction = 1)
    AND ((DATEPART(MONTH, pts_datetime) = 12) or (DATEPART(MONTH, pts_datetime) = 11))


Bookmarks

Posting Permissions

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