Thread: Updating and null values.

Hi!
Can anyone help me with this?

Say I have two tables :
tt(tx1 int4, tx2 oid,ty1 int4, ty2 oid );
xx(x1 int4, x2 oid)
yy (y1 int4, y2 oid)
I want to perform the following query:

UPDATE tt  SET tx1 =  A.x1,  ty1=B.y1  FROM xx A, yy B WHERE tx2 =  A.x2
AND ty2 = B.y2;

This works fine while there's always a matching tuple of xx and yy for
tx2 and ty2.  If one of the values doesn't find a matching tuple, then
the whole tt tuple isn't updated.

Example:
Suppose the following instances of my tables:


tt = (     (     ,  500,     ,100),           (     ,  600,     ,200 ),           (     ,  0,         ,300 ) )
xx= (    (2, 500),    (3, 600),    ( 4, 1000))
yy =(    (2,100),    (1,200) ,    (4, 300) )


The result of the query would then be:
tt = (     (    2 ,  500,   2  ,100),           (    3 ,  600,   1   ,200 ),           (     ,  0,         ,300 ) )
It only updated the first two tuples.
I wanted the third tuple to be updated too. It should have the new
values (     ,0    ,4,  300), as y1 is not null, so ty1 should now have
the same value as y1.(not a null one)

Does anyone have an idea on how to solve this?
Any help would be great.
Thanks.
   Ana Roizen.

Ana Roizen <aroizen@sinectis.com.ar> writes:
> I want to perform the following query:

> UPDATE tt  SET tx1 =  A.x1,  ty1=B.y1  FROM xx A, yy B WHERE tx2 =  A.x2
> AND ty2 = B.y2;

> This works fine while there's always a matching tuple of xx and yy for
> tx2 and ty2.  If one of the values doesn't find a matching tuple, then
> the whole tt tuple isn't updated.

It seems to me that you are asking for the two fields to be updated
independently, so why not just do two queries?

UPDATE tt  SET tx1 = A.x1 FROM xx A WHERE tx2 = A.x2;
UPDATE tt  SET ty1 = B.y1 FROM yy B WHERE ty2 = B.y2;

(You can use begin/end transaction if you want to ensure that no one
else can see the intermediate state of the table.)

I believe that UPDATE is acting as it should in the example you show.
If it worked the way you suggest, there would be no way to achieve
the other effect where you *don't* want an update to occur unless
matching records exist in both A and B.
        regards, tom lane

I use the following code to create a function:

create function employee_stats_update() returns int4as 'insert into employee_stats values ( new.employee_name); select
1as ignore_this'
 
language 'plpgsql';


Then I create the following trigger:

create trigger update_employee_statsbefore insert on employee_master for each row execute procedure
employee_stats_update();

This is what I get back when I try to create the trigger:

ERROR:  CreateTrigger: function employee_stats_update () does not exist

Adam

When I try to execute my insert, I am now getting this error:

java.sql.SQLException: ERROR:  stat failed on file
$/usr/local/pgsql/lib/plpgsql.so

What is it trying to do and why isn't it working.