user19120
user19120

Reputation: 337

Update query in Postgresql

I have two tables in postgresql. One is SateTable having column Statename and Statecode and another is DistTable having columns DistName, Statename and statecode. In the DistTable the columns DistName and Statename are populated. I want to update 'Statecode' column in the DistTable. Kindly help.

create table Statetable (statename varchar, statecode varchar); 
create table Disttable (Distname varchar, Statename varchar, statecode varchar); 

insert into Statetable 
values 
   ('new york','NY'), 
   ('Nebraska','NB'), 
   ('Alaska','AL'); 

insert into Disttable values 
   ('King','New York', null), 
   ('salt lake','Nebraska', null), 
   ('Hanlulu','AL', null);

Upvotes: 0

Views: 151

Answers (2)

user330315
user330315

Reputation:

What you asked for:

(which is the wrong solution)

update disttable 
  set statecode = st.statecode
from statetable st 
where  st.statename = disttable.statename;

BUT: the above statement will only update a single row from your sample data because

  1. 'new york' is not the same as 'New York'
  2. There is no statename 'AL' in the statetable.

so the join between the two tables will only find (and update) the row with Nebraska.

SQLFiddle demo: http://sqlfiddle.com/#!15/977fe/1


The correct solution:

The correct solution is to normalize your tables. Give the state table a proper primary key (statecode is probably a good choice) and git rid of the useless table suffix for your tables.

Create the states table with a primary key:

create table states 
(
   statecode varchar(2) not null primary key, 
   statename varchar
); 


insert into states (statename, statecode)
values 
   ('new york','NY'), 
   ('Nebraska','NB'), 
   ('Alaska','AL'); 

The distribution table only references the state table:

create table distributions 
(
   -- you are missing a primary key here as well.
   Distname varchar, 
   statecode varchar not null references states
); 

insert into distribution values 
   ('King','NY'), 
   ('salt lake','NB'), 
   ('Hanlulu','AL'); 

If you need to display the distname together with the statename, use a join:

select d.distname, 
       st.statename,
       st.statecode
from distribution d
  join states s on s.statecode = d.statecode;

If you don't want to type that all the time, create a view with the above statement.

This solution also avoids the problem that the UPDATE with a join doesn't find the corresponding rows because of incorrectly spelled states or wrong values for the state name.

Upvotes: 1

vencrena
vencrena

Reputation: 65

Corrcet me if I am wrong, but I think your model should be:

create table Statetable (statename varchar, statecode varchar); 
create table Disttable (distname varchar, statecode varchar); 

insert into Statetable 
values 
   ('new york','NY'), 
   ('Nebraska','NB'), 
   ('Alaska','AL'); 

insert into Disttable values 
   ('King','NY'), 
   ('salt lake','NB'), 
   ('Hanlulu','AL');

futhermore I think it would be wise to have some identification on disttable so later you can add other address tables.

Upvotes: 1

Related Questions