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.

• Erwin Brandstetter: How do you want to resolve duplicate entries for (name, date) like in the last two rows of employeerole?

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.