Question 113918 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL : Change column values based on another column value using some condition in same table

I have a table and want to replace the column value with value from other column value based on some condition.

 
+---------------------+
| Cntry | Code | Value |    
+---------------------+
| US  | C11  | A     | 
| US  | C12  | B     |
| US  | C13  | C     |
| US  | C14  | D     |
| US  | C15  | E     |
| UK  | C11  | A     | 
| UK  | C12  | B     |
| UK  | C13  | C     |
| UK  | C14  | D     |
| UK  | C15  | E     |

+---------------------+

I want to replace the value of C14 based on the value of C11 based on Cntry

So my output should be like this.

+---------------------+
| Cntry | Code | Value |
+---------------------+
| US  | C11  | A     | 
| US  | C12  | B     |
| US  | C13  | C     |
| US  | C14  | A     |<====Repalce with C11 for US
| US  | C15  | E     |
| UK  | C11  | G     | 
| UK  | C12  | B     |
| UK  | C13  | C     |
| UK  | C14  | G     |<====Repalce with C11 for UK
| UK  | C15  | E     |

+---------------------+

Is there anyway to do this in postgresql?

Thanks

Tags:

2

Answers


  1. 0

    Create sample data:

    CREATE TABLE table1 (
    cntry varchar NULL,
    code varchar NULL,
    value varchar NULL
);

INSERT INTO table1 (cntry, code, value) VALUES('US', 'C11', 'A');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C12', 'B');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C13', 'C');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C14', 'D');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C15', 'E');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C11', 'G');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C12', 'B');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C13', 'C');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C14', 'D');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C15', 'E');

    Sample query:

    select 
    t1.cntry, 
    t1.code, 
    case when t2.value is not null then t2.value else t1.value end as "value"
from table1 t1  
left join (
    select 
        cntry, 
        'C14' as code, 
        value 
    from table1
    where code = 'C11'
) t2 on t1.cntry = t2.cntry and t1.code = t2.code 

-- Result: 
cntry   code    value
US      C11     A
US      C12     B
US      C13     C
US      C14     A
US      C15     E
UK      C11     G
UK      C12     B
UK      C13     C
UK      C14     G
UK      C15     E
    Login or Signup to reply.
  2. 0

    If you want to actually change the contents of your table, then an UPDATE query will do the trick.

    UPDATE mytable 
   SET code = 'C11' 
 WHERE code = 'C14'`

    For obvious reasons, you should be super careful with UPDATE queries. There are a couple of ways to avoid mistakes that I sometimes use:

    1. Try a SELECT statement first to get the rows I think I want to change. If this looks good, then edit the query to change SELECT to UPDATE
    2. Make a copy of the table. Try your update on the copy. If you’re happy with the results, try the query on the original table. Use SELECT INTO to create table (SELECT * INTO tablecopy FROM mytable) and then DROP TABLE (DROP tablecopy) on the copy.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search