Thread: Updatable resultset with join query.

I am trying to get an updatable result set using a join query.
It does not work if I join it using where condition.
(FROM table1, table2 WHERE table1.id1 = table2.id2)

If I do it using INNER JOIN it works partially. What I mean
is I can update one table but not the other.

Example
sql = "SELECT table1.p_key, table1.name, table2.p_key, table2.f_key, table2.name
FROM table1 INNER JOIN table2 ON table1.p_key = table2.f_key;";

I will be able to update the columns in table1 but not in table2.

sql = "SELECT table1.p_key, table1.name, table2.p_key, table2.f_key, table2.name
FROM table2 INNER JOIN table1 ON table1.p_key = table2.f_key;";

In this case I can update columns in table2 but not in table 1.

So seems like the first table that comes in the FROM clause is updatable.

Is this how is should work?

Thanks,
-Prasanth.

Yes, not all result sets are updateable, only simple one table result
sets are supported. This is in the spec.

dave

Prasanth wrote:

>I am trying to get an updatable result set using a join query.
>It does not work if I join it using where condition.
>(FROM table1, table2 WHERE table1.id1 = table2.id2)
>
>If I do it using INNER JOIN it works partially. What I mean
>is I can update one table but not the other.
>
>Example
>sql = "SELECT table1.p_key, table1.name, table2.p_key, table2.f_key, table2.name
>FROM table1 INNER JOIN table2 ON table1.p_key = table2.f_key;";
>
>I will be able to update the columns in table1 but not in table2.
>
>sql = "SELECT table1.p_key, table1.name, table2.p_key, table2.f_key, table2.name
>FROM table2 INNER JOIN table1 ON table1.p_key = table2.f_key;";
>
>In this case I can update columns in table2 but not in table 1.
>
>So seems like the first table that comes in the FROM clause is updatable.
>
>Is this how is should work?
>
>Thanks,
>-Prasanth.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

On Thu, 7 Apr 2005, Prasanth wrote:

> I am trying to get an updatable result set using a join query.
> It does not work if I join it using where condition.
> (FROM table1, table2 WHERE table1.id1 = table2.id2)
>
> If I do it using INNER JOIN it works partially. What I mean
> is I can update one table but not the other.
>

It is actually a known bug that it works with the INNER JOIN.  There is
code to try and determine if the query spans more than one table, but it
is very simple and only recognizes plain "," joins.  Code was submitted to
improve this, but I did not take a good look at it.  In any case it
probably isn't the direction you want things to go anyway.

Kris Jurka