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

The role column is updated later.

and the other table is

    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(
                      ,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.

  • How do you want to resolve duplicate entries for (name, date) like in the last two rows of employeerole? Commented Jan 24, 2012 at 22:37

2 Answers 2


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.

UPDATE dim_sesid d
SET    role = e.roles
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.