Updating Multiple Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating Multiple Columns

Author

Topic

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-20 : 08:13:11

Hi, im trying to update multiple columns from a select query. The code i currently use which works is below, but is there a way of doing this in one piece os code instead of 2?

Thanks,

Andy

Update TestTable
Set  ColA =  
	(select count(Table2.ID)
	from Table2,Table1
	where Table2.InvoiceYear = TestTable.InvYear
	AND Table2.InvoiceMonth = TestTable.InvMonth
	AND Table2.ID = Table1.ID
	AND Table1.Code = 'AAA')

Update TestTable
Set ColB = 
	(select sum(Table2.Cost)
	from Table2,Table1
	where Table2.InvoiceYear = TestTable.InvYear
	AND Table2.InvoiceMonth = TestTable.InvMonth
	AND Table2.ID = Table1.ID
	AND Table1.Code = 'AAA')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 08:21:00

I suppose you could do that in a single query like shown below. But something does not seem right in the query. If I understood correctly, you have three tables - Table1, Table2 and TestTable. The way the query is written, are updating EVERY row in TestTable with the count and sum that you calculate from the other two tables. Is that really the intention?

UPDATE TestTable SET 
	ColA = Count1,
	ColB = Sum1
FROM
(
	SELECT
		COUNT(Table2.ID) AS Count1,
   		SUM(Table2.Cost) AS Sum1
	FROM
		Table2,
		Table1
	WHERE
		Table2.InvoiceYear = TestTable.InvYear
		AND Table2.InvoiceMonth = TestTable.InvMonth
		AND Table2.ID = Table1.ID
		AND Table1.Code = 'AAA'
)s

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 08:33:56

Sunita, look at the correlated subsqueries and find the two column reference to the outer table.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 08:36:31

[code]UPDATE tt
SET tt.ColA = w.Cnt,
tt.ColB = w.Cost
FROM dbo.TestTable AS tt
LEFT JOIN (
SELECT t2.InvoiceYear,
t2.InvoiceMonth,
SUM(t2.Cost) AS Cost,
COUNT(t2.ID) AS Cnt
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2 ON t2.ID = t1.ID
WHERE t1.Code = 'AAA'
GROUP BY t2.InvoiceYear,
t2.InvoiceMonth
) AS w ON w.InvoiceYear = tt.InvYear
AND w.InvoiceMonth = tt.InvMonth[/code]

N 56°04'39.26"
E 12°55'05.63"

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-20 : 08:44:40

Yes, that is my intention. The table, TestTable, contains a list of months and years, and this is updating so that it then contains the number of sales that month (the count) and the value of those sales (the sum).

When i run the code as you suggest I now get

Msg 4104, Level 16, State 1, Line 38
The multi-part identifier "TestTable.InvYear" could not be bound.
Msg 4104, Level 16, State 1, Line 38
The multi-part identifier "TestTable.InvMonth" could not be bound.

Thanks,
Andy

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-20 : 08:52:38

Thank You SwePeso, that works perfectly (once I remembered to rename everything back to their actual names :) - wow i'm an idiot for forgetting that to start with)

Thanks Again,
Andy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 09:04:19

quote:
Originally posted by SwePeso

Sunita, look at the correlated subsqueries and find the two column reference to the outer table.

N 56°04'39.26"
E 12°55'05.63"

CASE 
   WHEN me.BLIND = 'TRUE' THEN 'Shut up'
END;

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-20 : 09:17:04

quote:
Originally posted by sunitabeck
CASE
WHEN me.BLIND = 'TRUE' THEN 'Shut up'
END;

If only that could actually be hardcoded into all our brains.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources