Reputation: 337
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
Reputation:
(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
'new york'
is not the same as 'New York'
'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 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
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