Update Query Help - 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

Update Query Help

Author

Topic

bdenison
Starting Member

2 Posts

Posted - 2010-02-11 : 11:34:13

I am very new to SQL, so please be easy on me

I have...

Table_1

UID..Site..Structure
1.....A.....House
2.....A.....Barn
3.....A.....Well
4.....B.....House
5.....B.....Well
6.....C.....House

Table_2

Site...Structure
A
B
C

...and would like to run an update query to get...

Table_2

Site....Structure
A.......House, Barn, Well
B.......House, Well
C.......House

Any help would be greatly appreiciated, and please let me know if more information is required. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 11:40:54

if you're using SQL 2005 use 3rd scenario in this link
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 11:45:26

if you're on SQL 2000 create a function as below


CREATE FUNCTION dbo.StrucureListGet
(
@Site  varchar(10)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @List varchar(8000)

SELECT @List=COALESCE(@List + ',','') + Structure
FROM Table1
WHERE Site=@Site

RETURN @List
END 

and use it as 


UPDATE t2
SET t2.Structure= t1.Structure
FROM table2 t2
JOIN (SELECT DISTINCT Site,dbo.StrucureListGet(Site) AS Structure FROM Table1) t1
ON t1.Site=t2.Site

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bdenison
Starting Member

2 Posts

Posted - 2010-02-11 : 12:06:37

quote:
Originally posted by visakh16

if you're on SQL 2000 create a function as below

CREATE FUNCTION dbo.StrucureListGet
(
@Site varchar(10)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @List varchar(8000)

SELECT @List=COALESCE(@List + ',','') + Structure
FROM Table1
WHERE Site=@Site

RETURN @List
END

and use it as

UPDATE t2
SET t2.Structure= t1.Structure
FROM table2 t2
JOIN (SELECT DISTINCT Site,dbo.StrucureListGet(Site) AS Structure FROM Table1) t1
ON t1.Site=t2.Site

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you very much, this looks like it will do the trick. Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:08:40

welcome

Make sure you go through method in link too If you're on 2005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources