Quick SQL Question - update a single row with multiple rows

This is on SQL Server 2000 (if that matters, but I think this is just a
T-SQL issue).
I have two tables (tied together by a common key, in this example TableID),
the first of which has many rows (for a given ID), and the second has just
one row (for that ID).
I want to figure out a way to do an UPDATE query (without using a cursor)
that will allow me to build up the Descr(iption) field on the second table
with all of the values from the original table, concatenated.
For example, if the first table (which you'll see I create and populate in
the example below) contains:
TableID Counter
1 1
1 2
1 3
1 4
1 5
and the second table contains:
TableID Descr
1 Start:
I want to come up with an update query that will join the two tables, and
populate the Descr field of the single row of the second table (for TableID
1) with: "Start: 1, 2, 3, 4, 5".
And yet, I'm at a loss to figure out a way to do this (other that cursors,
that I need to avoid using).
Here's my code, for what it's worth:
DECLARE @.table1 table
(
TableId int,
Counter int
)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,1)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,2)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,3)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,4)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,5)
DECLARE @.Table2 table
(
TableID int,
Descr char(1024)
)
INSERT INTO @.Table2 (TableID, Descr)
VALUES (1, 'Start:')
UPDATE @.Table2
SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
FROM @.Table2 T2
INNER JOIN @.Table1 T1
ON T2.TableID = T1.TableID
select *
from @.Table2you want to create a udf to do concat...
e.g.
create function udf(@.id int)
returns varchar(1024)
as
begin
declare @.s varchar(1024)
select @.s=isnull(@.s+',','')+cast(@.counter as varchar)
from tb1
where id=@.id
return @.s
end
update tb2
set descr=udf(id)
-oj
"Scott M. Lyon" <scott.RED.lyon.WHITE@.rapistan.BLUE.com> wrote in message
news:ugO34pvPGHA.1556@.TK2MSFTNGP09.phx.gbl...
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example
> TableID), the first of which has many rows (for a given ID), and the
> second has just one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for
> TableID 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
>|||Scott M. Lyon wrote:
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example TableID)
,
> the first of which has many rows (for a given ID), and the second has just
> one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for TableI
D
> 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
Don't store the data in both forms in permanent tables. For one thing
you are creating redundancy. For another, "descr" looks like a
non-atomic value, which is a bad idea in principle. So assuming this is
just a one-off exercise a cursor may even be the most feasible
solution.
Assuming your data will be unchanging while you update Table2, take a
look at this example for one possible solution:
http://groups.google.co.uk/group/mi...5888972df4b3291
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141418003.533985.216310@.e56g2000cwe.googlegroups.com...
> Don't store the data in both forms in permanent tables. For one thing
> you are creating redundancy. For another, "descr" looks like a
> non-atomic value, which is a bad idea in principle. So assuming this is
> just a one-off exercise a cursor may even be the most feasible
> solution.
> Assuming your data will be unchanging while you update Table2, take a
> look at this example for one possible solution:
> http://groups.google.co.uk/group/mi...5888972df4b3291
> --
> David Portas, SQL Server MVP
>
This was actually just an overly simplified example, so I could figure out
how to do this, and then apply that to the real problem. The real issue is
that the source tables are actually a combination of three or four permanent
tables, and the destination (that I'm doing the update on) is a temp table,
just used for generating data for reporting.|||>> The real issue is that the source tables are actually a combination of th
ree or four permanent tables, and the destination (that I'm doing the update
on) is a temp table,just used for generating data for reporting. <<
In a tiered architecture, is done in the front end and not in the
database. It sounds likeyou want to have VIEW that collects the report
data and then you can arrange it anyway you wish with the front end.
Update a temp table from several base tables, one at a time, is an
awful way to write SQL. We prefer to have things happen "all at once"
and in procedural steps.