Update column by comparing date and string values together

Question

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.

Answer 1

If you want to ignore the time part and match where the days are the same then:

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role) 
                FROM EMPLOYEEROLE er
               WHERE substr(DS.NAME, 1, 8) || 'xxxxxx'
                  || substr(DS.NAME, 15, length(USERNAME))
               like to_char(thedate::timestamp, 'yyyymmdd') || 'xxxxxx' || USERNAME);

At the moment, for the first row, the to_char(thedate::timestamp, 'yyyymmdd') || er.username would return 20111012aaa which doesn't exist. By replacing the time portion with the same set of characters (xxxxxx) then we can ignore that and it becomes:

           WHERE '20111012' || 'xxxxxx'
              || 'aaa'
           like '20111012' || 'xxxxxx' || 'aaa'

The problem is though that you have two roles for 13th January 2012. The min will always return the day guard leaving the night guard superfluous.

Answer 2

UPDATE dim_sesid d
SET    role = e.roles
FROM (
    SELECT replace(left(thedate, 10), '-') AS day
         , username
         , string_agg(role, ', ') AS roles
    FROM   employeerole
    GROUP  BY 1, 2
    ) e
WHERE  e.day = left(d.name, 8)
AND    e.username = substring(d.name, 15) ~~ (d.name || '%')

Major points

• First I aggregate multiple entries per (username, day) in employeerole. I concatenate all roles for a user per day. Your question is unclear on that. You may want to use min() or max() instead, depending on what you actually want.

• Then I match the subquery on (username, day) in a FROM clause. This is generally preferable over a subquery in the SET command, that can return no rows, which would pointlessly update the NULL value in dim_sesid with NULL.

• As I do not know anything about usernames (Same length? Only letters? ...) I use a clause that covers all possibilities:

  AND    e.username = substring(d.name, 15) ~~ (d.name || '%')
  

  ~~ being the LIKE operator.

• left(d.name, 8) requires PostgreSQL 9.1 or later.
  Replace with substring(d.name, 1, 8) in earlier versions.

• string_agg(e.role) requires PostgreSQL 9.1 or later.
  Replace with array_to_string(array_agg(e.role), ', ' in earlier versions.

Should perform very well, as the string manipulation is kept to a minimum and pointless updates are avoided.