So I am trying to Update a contract table where the Contract Start Date is the latest date and the relevant employee id. The Contract Table stores all past information about the employee.
eg.
contract_tbl
+------------+------------+--------------------+-----------------+---------------+
|Contractid |EmployeeId |ContractStartDate |ContractEndDate | Position |
+------------+------------+--------------------+-----------------+---------------+
| 1 | 1 | 2012-12-13 | 2013-12-12 | Data Entry |
+------------+------------+--------------------+-----------------+---------------+
| 2 | 1 | 2014-01-26 | 2015-01-25 | Data Entry |
+------------+------------+--------------------+-----------------+---------------+
| 3 | 2 | 2014-01-26 | 2015-01-25 | Data Entry |
+------------+------------+--------------------+-----------------+---------------+
This is the SQL that I have but it does not work. (using a mysql db)
UPDATE contract_tbl
SET Position='Data Analyst'
WHERE EmployeeId = 1 And ContractStartDate= (
select max(ContractStartDate
FROM contract_tbl))
So it should Update the second row shown above with Data Analyst in the Position column but I am getting an error.
Does anybody have any idea how to fix this?
Thanks in advance