I have two tables, Tbl_M is a maste

[puredesi5]

I have two tables, Tbl_M is a master table, and Tbl_T is a transaction table.

Master table (Tbl_M) has following fields: empid, name, phonenum, fld_a, fld_b, etc

Transaction table (Tbl_T)has following fields: empid, name, phonenum, fld_x.

How would I get name, phone number for each empid from master to transaction table.

The following doesn’t work:

Update Tbl_T
Set Tbl_T.name = Tbl_M.name,
Tbl_T.phonenum = Tbl_M.phonenum
Where Tbl_T.empid = Tbl_M.empid

Any suggestion?

I know I can do it in a procedure by creating a cursor of all records on transaction table and then reading and updating it with matching records from master file.

Can I do it without procedure?

 

[tlbroadbent]

Does tbl_T already contain rows? If not, you'll need to insert the rows rather than update the table.

Insert tbl_t (empid, name, phonenum)
Select empid, name, phonenum
from tbl_m

When posting questions here it helps a lot if you give details and define what you mean by "doesn't work." Doesn't work could mean many things. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[topher0303]

You forgot the FROM clause:

Update Tbl_T
Set Tbl_T.name = Tbl_M.name,
Tbl_T.phonenum = Tbl_M.phonenum
From Tbl_T, Tbl_M
Where Tbl_T.empid = Tbl_M.empid


However, joining in the where clause is old school. The standard (ANSI) way to do it with the join clause is:

Update Tbl_T
Set Tbl_T.name = Tbl_M.name,
Tbl_T.phonenum = Tbl_M.phonenum
From Tbl_T Join Tbl_M On Tbl_T.empid = Tbl_M.empid


Chris.

 

[puredesi5]

Terry, yes the data is there on the transaction table. I just want to update data with some fields on the master table.

Chris, it worked! adding 'from tbl_T, tbl_m' did the job.

I thank both of you for your help.