July 3, 2006 at 7:36 am
For some reporting purpose I am creating a table on fly
Create #temp (
COL1 Varchar(1)
COL2 int
COL3 int
COL4 int
COL5 int
)
now I insert data in this table using some querie1 like
Insert into #temp(COL1, COL2) SELECT ........
Data inserted is as:::
A, 1
B, 23
C, 24
D, 25
Now I want to update COL3, COL4,COL5 with different queries, only thing common is COL1
SO if i want to update my #Temp for for COL3, I have a Select query which gives data like
A, 101
B, 203
C, 204
D, 205
How can I update #temp and updating data from select query for all the rows in Col1, COL1 will be same for all the different select queries I have for other columns
Please help, I am stuck...
Brij
July 3, 2006 at 8:20 am
July 3, 2006 at 8:29 am
Or maybe something like this?
--data
declare @t table (col1 varchar(1), col2 int, col3 int, col4 int, col5 int)
insert @t (col1, col2)
select 'A', 1
union all select 'B', 23
union all select 'C', 24
union all select 'D', 25
--calculation
update a set a.col3 = b.col3
from @t a inner join (
select 'A' as col1, 101 as col3
union all select 'B', 203
union all select 'C', 204
union all select 'D', 205) b on a.col1 = b.col1
select * from @t
/*results
col1 col2 col3 col4 col5
---- ----------- ----------- ----------- -----------
A 1 101 NULL NULL
B 23 203 NULL NULL
C 24 204 NULL NULL
D 25 205 NULL NULL
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 3, 2006 at 10:11 am
If you need to update the table just by joining the new resultsset you can do this by:
UPDATE #TempTable
SET COL3 = Query.Col3,
COL4 = Query.Col4,
COL5 = Query.Col5
FROM #TempTable T
INNER JOIN
(
SELECT COL3,COL4,COL5 FROM SomeSource
) Query
ON T.Col1 = Query.Col1
HTH, jens Suessmeyer.
---
---
July 3, 2006 at 10:21 am
You need to include Col1 in the subselect "Query" to do this..
I wasn't born stupid - I had to study.
July 4, 2006 at 2:48 am
Thanks you all..
Derived tables were the best solution..
Brij
July 4, 2006 at 2:52 am
I am using aggregate functions to update the columns in the table, so I had to use Derived table from derived tables, something like this:
update a set a.col3 = c.col3
from @t a inner join (
Select 'A' as Col1, Count(Col3) as Col3
FROM (
select 'A' as col1, 101 as col3
union all select 'B', 203
union all select 'C', 204
union all select 'D', 205) b
)C on a.col1 = c.col1
I was wondering, will this have any impact on performance, when I have to update aropund 10 columns with around 1 million records in the base table?
Thank you again.
Brij
July 4, 2006 at 6:20 pm
You only need the nested inline views (derived tables) because you are using
(select 'A' as col1, 101 as col3
union all select 'B', 203
union all select 'C', 204
union all select 'D', 205)
to stand in for an actual table (or view, TVF, etc), for testing/demo purposes.
In your actual query (i.e. where you are using values from the db, not literal values in the code), there would usually be no need to have a second inline view wrapped around the first, just to do the aggregation.
Re: performance, if you mean: is introducing aggregates into the query likely to degrade performance, there is really no answer. The two statements (with and without aggregation) do different things, so can't be ranked for performance. If you need to achieve a particular result, there's no point in asking whether it would be quicker or use less resources to do something different instead.
If you mean: does the inline view cause a performance hit, I think almost certainly not as the optimiser would eliminate it (but the optimiser can be unexpectedly obtuse sometimes). If it's redundant in your actual code, I would get rid of it, as a layer of unecessary complexity.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply