Updating Tabled based on other Table Criteria

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 Tabled based on other Table Criteria

Author

Topic

pcgirl365
Starting Member

5 Posts

Posted - 2007-10-16 : 19:13:27

Hello,

I read through several other forum posts to try and construct my query and want to confirm the query I am testing.

I want to update 1 table based on the criteria found in another table (Update t1 where t2.condition = true). I've tried various iterations of joins but continue to get errors.

Any simple queries one can share to get me on the right track would be appreciated.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 21:37:04

We need to see some more information... post some sample data from both tables and sample scenario where records need to be updated...at this point the info you provided is insufficient...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pcgirl365
Starting Member

5 Posts

Posted - 2007-10-16 : 22:21:28

Here is some sample info:

Table 1
Personidno
LName
FName
DeptNo

Table 2
Personidno
EELocation

Ex: I want to change the Location in table 2 based on the DeptNo in table 1.
Something to the effect of 'Update t2 set location = x where t1.deptno = y'

Hope this is sufficient

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 23:28:52

You have not provided sample data to work with..
here's my guess:


Update T2
Set T2.Location = 'X'
FROM Table2 T2
JOIN Table1 T1 on T1.Personidno = T2.Personidno
WHERE T2.deptno = 'y'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:32:24

[code]
Update T2
Set T2.Location = 'X'
FROM Table2 T2
JOIN Table1 T1 on T1.Personidno = T2.Personidno
WHERE T1.deptno = 'y'
[/code]
Ya gotta read the question dinakar

Kristen

pcgirl365
Starting Member

5 Posts

Posted - 2007-10-17 : 07:37:43

Thanks to those that responded; I kept ommitting the 'From Table' statement. I was able to use this for 2 items last night and they both resulted very well.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-17 : 08:00:33

some methodology pointers
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-17 : 12:21:58

quote:
Originally posted by Kristen

Update T2
Set T2.Location = 'X'
FROM Table2 T2
JOIN Table1 T1 on T1.Personidno = T2.Personidno
WHERE T1.deptno = 'y'

Ya gotta read the question dinakar

Kristen

Yep

sorry...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources