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

[Tryst]

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:

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

[swampBoogie]

You need to use a scalar subquery

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.

[Tryst]

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

[Tryst]

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...

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

[swampBoogie]

If you only reference the columns directly you can use a statement like

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.

[Tryst]

Ok, thanks, swampboogie

my final query ended up looking like this.

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))