Updating fields from one table into another

[irbk]

First off, let me say that this is not my database. It's a system that was created by some one that has no idea of how to use a database. They are basically using this database as a bunch of seperate tables to store info. A majority of the tables are not linked, and no normilization has taken place. This being said, here is my issue. I have 2 tables that I'm working with. One is the workstat table that contains a dept_num and dept field. The other is a tiuser table that contains a dept_num and dept field (try to hold back the screams please, like I said, it's not my database). The dept_num and dept in the tiuser table is correct. The dept_num and dept in the workstat table is not. What I want to do is write a query that will make the values of dept_num and dept in workstat = to the dept_num and dept in tiuser. The 2 tables contain a userid field and that's what I would be "joining" on. So, the basic query I want to write is

update workstat
set workstat.dept_num = tiuser.dept_num
set workstat.dept = tiuser.dept
where workstat.userid = tiuser.userid

But it just dosen't work that way and for the life of me I can't figure it out. Thanks in advance!

 

[gmmastros]

You only use 1 set, but seperate field with commas and you need to join the 2 tables in a from clause.

update workstat
set    workstat.dept_num = tiuser.dept_num,
       workstat.dept = tiuser.dept
From   WorkStat
       Inner Join tiuser 
         On workstat.userid = tiuser.userid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

Try this:

Update   w
set      w.dept_num = t.dept_num,
         w.dept = t.dept
from     workstat w
         inner join tiuser t
         on (w.userid = t.userid)

Regards,
AA

 

[irbk]

I think that worked! I only tried it on a small sample just to be safe

update workstat
set workstat.dept_num = tiuser.dept_num
from workstat
inner join tiuser
on workstat.userid = tiuser.userid
where workstat.userid = 11

and now userid 11 is showing the same dept_num in both fields! YEA!!! THANK YOU THANK YOU THANK YOU!!!!!