Postgres - change column data

Question

Have a problem with update some rows in table 'commission'. Now I have commission_number like bar/123/456. I'd like to rename it to John/123/456 etc, taking a name from 'user' table

commission                       user
-------------------------        -----------------
commission_number|user_id         username|user_id
-------------------------        -----------------
bar/123/456      | 1               John   | 1
bar/123/123      | 2               Bob    | 2
bar/456/123      | 3               Thomas | 3

Below is my query, working with 'baz'. But don't know how to place 'username' from 'user' table. I only need to replace 'baz', and use username from 'user', but don,t know how.

UPDATE commission
SET commission_number = overlay(commission_number placing 'baz' FROM POSITION('bar' IN commission_number) for 3)
WHERE commission_number in (1,2,3,4,5,6,7,8)

Why do you store the username in the commission_number at all? You shouldn't be storing data that can be derived from the existing content. — user330315
– user330315, Commented Mar 12, 2013 at 13:40
I found a bug in system in our company and have to rename it. I cant change table structure, I'm not a developer :( — ssuperczynski
– ssuperczynski, Commented Mar 12, 2013 at 13:44
Then tell whoever is responsible that this is a horrible table design (you may quote me). — user330315
– user330315, Commented Mar 12, 2013 at 13:47

user330315 · Accepted Answer · 2013-03-12 13:47:21Z

3

update commission 
   set commission_number = u.username || '/' || left(commission_number, -strpos(commission_number, '/'))
from users u
  where u.user_id = commission.user_id

answered Mar 12, 2013 at 13:47

user330315

Sign up to request clarification or add additional context in comments.

Comments

AjV Jsy · Accepted Answer · 2013-03-12 13:49:43Z

1

I'd start by experimenting with something like...

UPDATE commission
SET commission_number = 
  (SELECT UserName FROM User WHERE user.user_id=commission.user_id)
   ||SubString(commission_number,4,8000)

There's also right(commission_number,-3) to get all but the first 3 chars from that.

I'm not sure what you're trying to do with
WHERE commission_number in (1,2,3,4,5,6,7,8) ?

edited Mar 12, 2013 at 13:49

answered Mar 12, 2013 at 13:39

AjV Jsy

6,1054 gold badges39 silver badges30 bronze badges

3 Comments

PM 77-1 Over a year ago

Do you need to TRIM UserName before concatenation?

user330315 Over a year ago

If the commission number has a different (longer) prefix than bar this will do the wrong thing.

AjV Jsy Over a year ago

Yes, if that was a requirement then you'd need to look towards using replace if you know what you're replacing, otherwise your technique of looking for the first / is a good one :)

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-03-12 13:48:41Z

This illustrates how the data will look after the update. I use this kind of select statement a lot when I'm working with string functions.

select c.commission_number, c.user_id, 
       (select username 
        from "user" 
        where "user".user_id = c.user_id) username,
       overlay(c.commission_number 
               placing (select username 
                        from "user" 
                        where "user".user_id = c.user_id) 
               from 1 for 3)
from commission c;

Now you can write the update statement in terms that you know will work.

update commission 
set commission_number = overlay(commission_number 
                                placing (select username 
                                         from "user" 
                                         where "user".user_id = commission.user_id) 
                                from 1 for 3);

Prahalad Gaggar · Accepted Answer · 2013-03-12 14:05:37Z

1

Try this query:

 update commission
 set commission_number=user_table.user_name+
  SUBSTRING(SUBSTRING(commission.commission_number,CHARINDEX('/',commission.commission_number,1)+0,LEN(commission.commission_number))
                ,1
                ,CHARINDEX('/',SUBSTRING(commission.commission_number,CHARINDEX('/',commission.commission_number,1)+1,LEN(commission.commission_number)),1)+LEN(commission.commission_number))
 from commission
 inner join user_table on user_table.user_id=commission.user_id

Verify on Sql Fiddle

Took a bit of time to show on SQL Fiddle

edited Mar 12, 2013 at 14:05

answered Mar 12, 2013 at 13:53

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

Collectives™ on Stack Overflow

Postgres - change column data

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related