0

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

xerocool
  • 13
  • 3

2 Answers2

-1

Probably this is what you want:

UPDATE contract_tbl c1
SET Position='Data Analyst' 
WHERE EmployeeId = 1 And ContractStartDate= (
  SELECT max(ContractStartDate)
  FROM contract_tbl c2
  WHERE c2.EmployeeId = c1.EmployeeId
)
Barmar
  • 741,623
  • 53
  • 500
  • 612
-1

This will also do:

UPDATE contract_tbl a
JOIN (
  SELECT MAX(ContractStartDate) m
  FROM contract_tbl
  WHERE EmployeeId = 1) b ON a.ContractStartDate = b.m AND a.EmployeeId = 1
SET a.Position='Data Analyst';
Fabricator
  • 12,722
  • 2
  • 27
  • 40