Re: update a row in one table with values from muliple rows in another table
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 09/20/04
- Next message: Peter Rilling: "How to allow mutiple application to use the sysmessages table without conflicting?"
- Previous message: MS User: "Re: UPDATE - Lock \ Network Error"
- In reply to: Derek Ruesch: "update a row in one table with values from muliple rows in another table"
- Next in thread: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Reply: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 17:06:45 -0500
Not easily. This kind of thing sort of goes against the grain. If you were
wanting to display it, you would have already recieved 20 replies saying
things like: "Let the UI do it." or "Let the presentation layer present
it." Technically, I must state that it is a generally bad idea, as Table2
is now a violator of the first normal form, in that you are trying to store
multiple values in it. Of course, if you are doing this for display
purposes, let the UI do it.
You could build a User defined function that builds the string "Dave, Brad"
based on the input of ID = 1. You would need a cursor of sorts to build
this output. There is also a "trick" that works most of the time (it may
fail for some sorting criteria, so it is generally considered a bad idea,
but it "usually" works and is often considered an acceptable hack (use it at
your own risk):
create table table1
(
id int,
name varchar(50),
primary key (id, name)
)
insert into table1
values (1,'bill')
insert into table1
values (1,'dave')
go
create function table1$denormalizeName
(
@id int
)
returns varchar(300)
as
begin
declare @output varchar(300)
set @output = ''
select @output = @output + name + ','
from table1
where id = @id
set @output = substring(@output,1,len(@output)-1)
return @output
end
go
select dbo.table1$denormalizeName(1)
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Derek Ruesch" <anonymous@discussions.microsoft.com> wrote in message news:402801c49f54$7d5ad2f0$a401280a@phx.gbl... > I have to following tables: > > Table1 > ----------------- > ID (int - Primary Key) | Name (varhcar 50 - Primary Key) > 1 | Dave > 1 | Brad > > Table2 > ----------------- > ID (int - Primary Key) | Names (varchar 300) > 1 | <NULL> > > I want to update the "Names" column for "ID" 1 in > the "Table2" table with the names that are linked to "ID" > 1 in the "Table1" table. > > I wrote the following query to accomplish this but the > only name that was entered into the "Names" column in > the "Table2" table was "Dave" (I wanted "Dave, Brad" > entered here.). > > UPDATE Table2 > SET Names=Names + ', ' + Name > FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID > WHERE Table1.ID=1 > > Is there any way that I can accomplish this? > > Thanks for the help. > > Derek Ruesch > > > >
- Next message: Peter Rilling: "How to allow mutiple application to use the sysmessages table without conflicting?"
- Previous message: MS User: "Re: UPDATE - Lock \ Network Error"
- In reply to: Derek Ruesch: "update a row in one table with values from muliple rows in another table"
- Next in thread: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Reply: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
