Updating a table with data from other table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Updating a table with data from other table

Author

Topic

bl4tech
Starting Member

2 Posts

Posted - 2007-10-20 : 08:55:22

I apologize if my question sounds too basic...

I have two tables as described below:

tblONE
----------
ID Number(9,0) not null - Primary key
Holding Char(250)

tblTWO
----------
ID Number(9,0) not null - Repeatable
Holdind Char(30)

Data examples of tblONE (see that Holding fields are empty)
------------------------------------
ID Holding
001
002
003
004
005

Data examples of tblTWO
------------------------------------
ID Holding
001 v.1, n.1, 1989
002 v.22, n.13, 1990
002 v.23, n.14, 1991
003 v.56, n. 2, 2002
004 v.87, n. 20, 2001
004 v.54 , n. 3, 2006
004 v.55, n. 4, 2007
005 v.3, n. 5, 1998

What I want is updating HOLDING field of tblONE with the content of HOLDING field from tblTWO, like that:
(see that ID field is UNIQUE in tblONE and it can occours more than once in tblTWO)

NEW Data examples of tblONE
--------------------------------------------
ID Holding
001 v.1, n.1, 1989
002 v.22, n.13, 1990 -- v.23, n.14, 1991
003 v.56, n. 2, 2002
004 v.87, n. 20, 2001 -- v.54, n. 3, 2006 -- v.55, n. 4, 2007
005 v.3, n. 5, 1998

TIA,

Bill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-20 : 09:21:45

quote:
see that ID field is UNIQUE in tblONE and it can occours more than once in tblTWO

So which record do you want in tblTWO do you want for such case ?

KH
[spoiler]Time is always against us[/spoiler]

hjyoungii
Starting Member

3 Posts

Posted - 2007-11-06 : 11:37:38

update tblone
set tblone.holding=tbltwo.holding
from tbltwo.holding
where tblone.id=tbltwo.id

quote:
Originally posted by bl4tech

I apologize if my question sounds too basic...

I have two tables as described below:

tblONE
----------
ID Number(9,0) not null - Primary key
Holding Char(250)

tblTWO
----------
ID Number(9,0) not null - Repeatable
Holdind Char(30)

Data examples of tblONE (see that Holding fields are empty)
------------------------------------
ID Holding
001
002
003
004
005

Data examples of tblTWO
------------------------------------
ID Holding
001 v.1, n.1, 1989
002 v.22, n.13, 1990
002 v.23, n.14, 1991
003 v.56, n. 2, 2002
004 v.87, n. 20, 2001
004 v.54 , n. 3, 2006
004 v.55, n. 4, 2007
005 v.3, n. 5, 1998

What I want is updating HOLDING field of tblONE with the content of HOLDING field from tblTWO, like that:
(see that ID field is UNIQUE in tblONE and it can occours more than once in tblTWO)

NEW Data examples of tblONE
--------------------------------------------
ID Holding
001 v.1, n.1, 1989
002 v.22, n.13, 1990 -- v.23, n.14, 1991
003 v.56, n. 2, 2002
004 v.87, n. 20, 2001 -- v.54, n. 3, 2006 -- v.55, n. 4, 2007
005 v.3, n. 5, 1998

TIA,

Bill

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-09 : 11:05:32

declare @Holding varchar(250), @ID int
declare csr cursor for
select ID from @tblONE
----------------------------
OPEN csr
FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS=0
BEGIN
set @Holding=''
select @Holding= @Holding + (case when @holding <> '' then '--' else '' end) + Holding
from @tblTWO
where ID=@ID
-- update
UPDATE @tblONE
SET Holding=@Holding
where ID=@ID
-- next
FETCH NEXT FROM csr INTO @ID
END

-- check
select * from @tblONE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources