I have problem in updating table.
I need to update table by comparing date and name with another table. I have two tables:
table 1 : dim_sesid name(varchar) role 20111012133513aaa123 20110908072611aaa121 20111002210235bbb853 20120113113353bbbl971
The role column is updated later.
and the other table is
employeerole username(varchar) role(varchar) thedate(varchar) aaa technician 2011-10-12 14:35:13 aaa technician 2011-09-08 07:26:11 aaa technician 2011-09-08 07:26:11 bbb day guard 2011-10-02 20:02:35 bbb day guard 2012-01-13 10:33:53 bbb night guard 2012-01-13 21:30:00
I want to update role in the dim_sesid table depending upon username and thedate. The same user can have different role in different date. There is no common column in two tables and the only column to perform join is "name" in dim_sesid. There are some duplicates values in employeerole.
I write the following query to update the dim_sesid table.
UPDATE dim_sesid ds SET role = (SELECT min (er.role) FROM EMPLOYEEROLE er WHERE SUBSTR( ds.name ,1 ,length(to_char(thedate::timestamp, 'yyyymmdd') || er.username)) = to_char(thedate::timestamp, 'yyyymmdd') || er.username );
But the problem is, no rows are updated.
If I check with
to_char(thedate::timestamp, 'yyyymmddhh24miss') condition, then it checks both date and time, but i want to compare only with date and username.
I am wondering how to check only 'name' and 'date' columns in two tables.