General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Updating a field from another table
Hi everyone,
I was wondering if somone can help me with the code that I need for a form. I have two tables where the relationship between the two is the employee number and I would like for all records that do not have "0" for the value of one particular field to be updated according to the corresponding employee number and date from the other table.
i.e.
Employee Table1
+---------+-------------+-------------+
| emp_num | Commission | RptDate |
---------------------------------------
5 8.00 10/15/01
7 0.00 10/15/01
9 4.00 10/12/01
Employee Table2
+----------+-------------+-------------+
| emp_num | Commission | RptDate |
----------------------------------------
5 0.00 10/15/01
7 0.00 10/15/01
9 0.00 10/15/01
12 0.00 10/15/01
So I would want only records that match the date I specify (in this case 10/15/01) to be updated so the commission for Table 2 employee number 5 should be the only field updated to say 8.00
The field for the commission for table2 may have values other than "0.00" but the record should only be updated to Table1's commission field value if the dates and employee number match as there will be duplicate employee numbers but distinct dates for each.
What select statement will accomplish this?
Thanks in advance!
Max
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only