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

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

CodePudding user response：

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

CodePudding user response：

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:

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