Updating a coloumn value by comparing coloumns from two tables

[dhillarun]

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.tab 1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

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

Pl. do reply me.

[rski]

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.tab 1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# \d tab3
Table "public.tab 3"
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;

[dhillarun]

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

[amitpatel66]

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.tab 1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# \d tab3
Table "public.tab 3"
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

[dhillarun]

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.tab 2"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

[rski]

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;

[dhillarun]

Sorry my mistake
update tab1 set c=tab2.c from tab2 where tab1.a=tab2.a;

Thanks ... It's working now.