By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

Updating a coloumn value by comparing coloumns from two tables

P: 18
Hi all,

I need to update field three of first table with field three of second
tables for all records that match field one of first table with field
one of second table (both are primary key for each table and are same).

I need to write an ANSI sql query to do this job.

I tried follwing , but it didn't work.

update tab1 set t1.c=t2.c from tab1 t1,tab2 t2 where t1.a=t2.a;

testdb=# \d tab1
Table "public.tab1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# \d tab3
Table "public.tab3"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

Pl. do reply me.
Sep 19 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 700
Hi all,

I need to update field three of first table with field three of second
tables for all records that match field one of first table with field
one of second table (both are primary key for each table and are same).

I need to write an ANSI sql query to do this job.

I tried follwing , but it didn't work.

update tab1 set t1.c=t2.c from tab1 t1,tab2 t2 where t1.a=t2.a;

testdb=# \d tab1
Table "public.tab1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# \d tab3
Table "public.tab3"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

Pl. do reply me.
It should works

update tab1 set tab1.c=tab2.c from tab2 where tab1.a=tab2.a;
Sep 19 '07 #2

P: 18
It should works

update tab1 set tab1.c=tab2.c from tab2 where tab1.a=tab2.a;
Sorry I am getting following error.

testdb=# update tab1 set tab1.c=tab2.c from tab2 where tab1.a=tab2.a;
ERROR: syntax error at or near "." at character 21
Sep 20 '07 #3

amitpatel66
Expert 100+
P: 2,367
Hi all,

I need to update field three of first table with field three of second
tables for all records that match field one of first table with field
one of second table (both are primary key for each table and are same).

I need to write an ANSI sql query to do this job.

I tried follwing , but it didn't work.

update tab1 set t1.c=t2.c from tab1 t1,tab2 t2 where t1.a=t2.a;

testdb=# \d tab1
Table "public.tab1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# \d tab3
Table "public.tab3"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

Pl. do reply me.
You have used tab2 in the query, but from the above table structure, I see table name as tab3
Sep 20 '07 #4

P: 18
You have used tab2 in the query, but from the above table structure, I see table name as tab3
Actually I need to update one table value into another table value.
Sry, I made that mistake . it should be 'tab2' only.

testdb=# \d tab2
Table "public.tab2"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
Sep 25 '07 #5

Expert 100+
P: 700
Sorry I am getting following error.

testdb=# update tab1 set tab1.c=tab2.c from tab2 where tab1.a=tab2.a;
ERROR: syntax error at or near "." at character 21
Sorry my mistake
update tab1 set c=tab2.c from tab2 where tab1.a=tab2.a;
Sep 25 '07 #6

P: 18
Sorry my mistake
update tab1 set c=tab2.c from tab2 where tab1.a=tab2.a;
Thanks ... It's working now.
Sep 27 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.