Hi,

How to create a rule which when an insert appears, update another row in
another table with the value of the insert.

for exemple :
Table myins(col10,col11,col12)
Table myupd(col20,col21,col22)

When : Insert into myins(col10,col11,col12)VALUES(1,2,3);
I want : update myupd set col21=col11, col22=col12 WHERE col20=col10;

but if I write this rule like this :
CREATE RULE regle1 AS
ON INSERT TO myins DO update
SET col21=NEW.col11, col22=NEW.col12
WHERE col20=NEW.col10;

and i write Insert into myins(col10,col11,col12)VALUES(1,2,3);

This rule update all the rows of the table myupd. (Because I don't know
how to specify/identify values/id of the inserted row).
But I want to update only the row with col20=1.

Thanks

bcar <bcar44 (AT) laposte (DOT) net> wrote:
Works for me:

test=# create table myins (id int, val int);
CREATE TABLE
test=*# create table myupd (id int, val int);
CREATE TABLE
test=*# commit;
COMMIT
test=# create rule my_rule as on insert to myins do update myupd set val=new.val where id=new.id;
CREATE RULE
test=*# insert into myupd values (1,11);
INSERT 0 1
test=*# insert into myupd values (2,22);
INSERT 0 1
test=*# insert into myins values (1,100);
INSERT 0 1
test=*# select * from myupd ;
id | val
----+-----
2 | 22
1 | 100
(2 rows)



Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Exact, Andreas is right, it's works.

Sorry about that thread, it's work, I just try with a bad table in my
"WHERE" clause so I have a bad JOIN and so bad result at the end.

And thank you Andreas for testing and confirming this case work in your
system


akretschmer (AT) spamfence (DOT) net a écrit :