Updating table directly from another 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

Transact-SQL (2000)

Updating table directly from another Table

Author

Topic

amitgujrathi
Starting Member

17 Posts

Posted - 2004-05-28 : 11:15:44

Is it possible to update a Table with data from another single row table??
e.g:
UPDATE T1
FROM (SELECT * from T2
WHERE T2C1 = <CND2>)
WHERE T1C1 = <CND1>

Here T1 & T2 have exact same table structures

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-05-28 : 11:25:17

Are you trying to insert the data from T2 into T1 ?

Insert into T2
select * from T1
where ...

-- PS - don't use select * - specify the required columns

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

amitgujrathi
Starting Member

17 Posts

Posted - 2004-05-28 : 15:23:13

No, I'm not trying to insert from T2 to T1. I want to update based on a conditions CND2, CND1.

Also, I just gave an example with "select * from T2", since T1 & T2 have more than 200 fields, thats why? I know I have to specify list of fields....

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-05-28 : 15:29:19

gonna have to join the tables somehow. hard to say without knowing your data set.

UPDATE t1
from t1
inner join t2 on t1.t1c1 = t2.t2c2
where t1c1 = cnd2

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-05-31 : 01:01:42

As Daniel point's out, you need some kind of join here.

Give us some test data ( not the full 200 feilds, but say 5 or 6, and how you want them to change). I get the feeling you might be looking at using CASE, or something like that.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-05-31 : 08:04:36

if u want to update the entire table with all the columns, then u need to specify them explicitly in ur update statement.
Here i assume that there is a common field in both the tables and i do an update. Hope its usefull

update t1 set col1=b.col1
from t1 a,(SELECT col1,col2 from T2
WHERE T2.C1 = <CND2>) b
where a.col1=b.col1

regards,
Ganesh

Enjoy working

amitgujrathi
Starting Member

17 Posts

Posted - 2004-06-04 : 16:35:43

Thanks guys.
I had got the solution using the similar method specified by Ganesh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources