update table2 values to table1

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)

update table2 values to table1

Author

Topic

varmamkm
Starting Member

8 Posts

Posted - 2008-01-23 : 00:08:01

Hi, Please help me out

--I hava two tables with following information
create table table1(empid int, empname varchar(20), empcity varchar(20), empaddress varchar(20))
insert into table1 values(1,'a','a1','a2')

create table table2 (field varchar(20), value varchar(20))
insert into table2 values('empcity','a11')
insert into table2 values('empaddress','a22')

--table1 returns the following result set
select * from table1
empid | empname | empcity | empaddress
---------------------------------------
1 | a | a1 | a2

--table2 returns the following result set
select * from table2
field | value
---------------------
empcity | a11
empaddress | a22

--I want the table2 information to be updated in table1 ie empcity and empaddress
--The final result set to be with the updated information of table2
empid | empname | empcity | empaddress
---------------------------------------
1 | a | a11 | a22

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 01:03:45

Can you elaborate your problem? Tables provided are not related. On what condition you want data to be updated in table1 from table2.

varmamkm
Starting Member

8 Posts

Posted - 2008-01-23 : 01:39:52

table2 fields(row values) are same as table1 column names

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 01:55:38

I assume you are looking for following

Create table #emp
( empid int,
empname varchar(20),
empcity varchar(20),
empaddress varchar(20)
)

Insert into #emp values (1,'a','a1','a2')
Insert into #emp values (2,'b','b1','b2')
Insert into #emp values (3,'c','c1','c2')

Create table #empAdd
( empid int,
empcity varchar(20),
empaddress varchar(20)
)

Insert into #empAdd values (1,'a11','a22')

Insert into #empAdd values (2,'b11','b22')

Insert into #empAdd values (3,'c11','c22')

--select * from @emp
--select * from @empAdd

Update #emp
set #emp.empcity=#empAdd.empcity,#emp.empaddress=#empAdd.empaddress
from #emp inner join #empAdd on #emp.empid=#empAdd.empid

select * from #emp
select * from #empAdd

varmamkm
Starting Member

8 Posts

Posted - 2008-01-23 : 02:45:47

no.

table2 having only two columns -- field and value
here field is nothing but column name of table1
ex: table2 field "empcity" is equal to table1 column name "empcity".. ie i have to compare the table2 field value with table1 column name and update the value...

hope this is clear...

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 03:50:38

You gave --table1 returns the following result set
select * from table1
empid | empname | empcity | empaddress
---------------------------------------
1 | a | a1 | a2

--table2 returns the following result set
select * from table2
field | value
---------------------
empcity empaddress
a11 a22

I am not sure what comparision you are making. I see no linkage between tabl1 and table2. like, in Table1, for empcity you have 'a1' and in Table2 you have 'a11'. How are you going to compare and update table1. Is there any primary-foreign key relationship betwen two tables. Be more specific and clear in your requirement and provide more sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 04:23:39

quote:
Originally posted by varmamkm

no.

table2 having only two columns -- field and value
here field is nothing but column name of table1
ex: table2 field "empcity" is equal to table1 column name "empcity".. ie i have to compare the table2 field value with table1 column name and update the value...

hope this is clear...

You need dynamic sql to achieve the result. something like:-

DECLARE @ID int,@Sql nvarchar(4000),@tablefield varchar(100),@tablevalue varchar(50)

SELECT @ID=MIN(t.RowNo)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [value]) AS RowNo,
field,
[value]
FROM table2
)t

/* Execute the string with the first parameter value. */

WHILE @ID IS NOT NULL
BEGIN


SELECT @tablefield=t.field,@tablevalue=t.[value]
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [value]) AS RowNo,
field,
[value]
FROM table2
)t
WHERE t.RowNo=@ID
SELECT @tablefield,@tablevalue

SELECT @Sql='UPDATE table1
SET  ' + @tablefield + '  = ''' + @tablevalue + ''''

EXEC (@sql)

SELECT @ID=MIN(t.RowNo)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [value]) AS RowNo,
field,
[value]
FROM table2
)t
WHERE t.RowNo>@ID


END

select * from table1

varmamkm
Starting Member

8 Posts

Posted - 2008-01-23 : 05:01:16

I got it... thanks for your help...

varmamkm
Starting Member

8 Posts

Posted - 2008-01-23 : 05:24:57

hi sunil... sorry for the confusion... based on visakh inputs i have written a stored procedures to get the required output...

alter procedure mydata
as
begin
declare @query varchar(100)
declare @field varchar(20)
declare @value varchar(20)

declare curnext cursor for select field, value from TABLE2
open curnext
fetch next from curnext into @field, @value
while @@fetch_status = 0
begin
set @query = 'update table1 set ' + @field + '=''' + @value + ''''
print @query
exec (@query)
fetch next from curnext into @field, @value
end
close curnext
deallocate curnext
select *from table1
end

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-24 : 11:42:05

quote:
Originally posted by varmamkm

Hi, Please help me out

--I hava two tables with following information
create table table1(empid int, empname varchar(20), empcity varchar(20), empaddress varchar(20))
insert into table1 values(1,'a','a1','a2')

create table table2 (field varchar(20), value varchar(20))
insert into table2 values('empcity','a11')
insert into table2 values('empaddress','a22')

--table1 returns the following result set
select * from table1
empid | empname | empcity | empaddress
---------------------------------------
1 | a | a1 | a2

--table2 returns the following result set
select * from table2
field | value
---------------------
empcity | a11
empaddress | a22

--I want the table2 information to be updated in table1 ie empcity and empaddress
--The final result set to be with the updated information of table2
empid | empname | empcity | empaddress
---------------------------------------
1 | a | a11 | a22

What if table1 has more than 1 row, and table2 has more than 2 rows?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources