update problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

update problem

Author

Topic

dinzg
Starting Member

6 Posts

Posted - 2005-02-12 : 08:34:29

I have Table: players and Query: years

players

ID
surname
---------------
years

ID
surname
age
---------------

I need to update players with field (age) in years with this result:

ID surname age

UPDATE players LEFT JOIN years ON players.ID= years.ID

I do not think this one will work

Can somebody please help

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 09:40:56

update players
set age = years.age
from players
join years
on players.ID = Years.ID

What do you mean by a query?
You would have to use the query in the update statement or use a derived table or create a temp table from the query before the update.
Use a left join in the above to update age to null if it's not returned by the query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dinzg
Starting Member

6 Posts

Posted - 2005-02-12 : 09:53:38

I have Ouery: years which counts age of players, so I want to insert age to Table: players

and I dont know how to do it..

ID & surname are same (in years and players). I just want to put age into players..

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 10:25:52

Mavbe create a temp table from the query is easiest.
After the select clasue put into #years

select ID, age
into #years
from....

then

update players
set age = #years.age
from players
join #years
on players.ID = #Years.ID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 10:49:06

Sent me an email and this is in access.
It will be

UPDATE Players INNER JOIN Years ON Players.ID = Years.ID SET Players.Age = [Years].[Age]

There is an access forum on this site - this one is for sql server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources