I am facing a difficult problem with setting up a rule.

The rule is essentially designed to allow updates to a view made up of
data from two tables.

Here is a simplified example to reproduce the problem:

--
-- clear the table and view
--

DROP VIEW IF EXISTS full_view;
DROP TABLE IF EXISTS child_table;
DROP TABLE IF EXISTS base_table;

--
-- create the tables
--

CREATE TABLE base_table
(

id INTEGER,
base_data CHAR(1)

);

CREATE TABLE child_table
(

id INTEGER,
child_data CHAR(1)

);

--
-- create the view
--

CREATE VIEW full_view AS
(

SELECT
base.id,
base.base_data,
child.child_data
FROM base_table AS base
INNER JOIN child_table AS child ON child.id = base.id

);

--
-- create update rule
--

CREATE RULE view_update AS
ON UPDATE TO full_view
DO INSTEAD
(

UPDATE base_table
SET
id = NEW.id,
base_data = NEW.base_data
WHERE id = OLD.id;

UPDATE child_table
SET
id = NEW.id,
child_data = NEW.child_data
WHERE id = OLD.id;

);

--
-- insert some data
--

INSERT INTO base_table (id, base_data) VALUES (1, 'a');
INSERT INTO child_table (id, child_data) VALUES (1, 'b');

--
-- everything is OK so far
--

SELECT * FROM full_view;

--
-- this query works as expected
--

UPDATE full_view SET child_data = 'c' WHERE base_data = 'a';

--
-- child_data has been updated
--

SELECT * FROM full_view;

--
-- this query does not work as expected
--

UPDATE full_view SET child_data = 'd', base_data = 'e' WHERE base_data =
'a';

--
-- child_data has not been updated, even though base_data was
--

SELECT * FROM full_view;

I think I understand why this happens. It seems logical that the first
query of the update rule is being run and then the second matches no
rows for "base_data = 'a'" because it was updated by the first.

Can anyone think of a workaround that would let me achieve this
functionality? Like, perhaps "caching" the resulting changes from the
first update query and only applying them after the second is run?

---
Erin Millard
Systems Analyst
I-Nex Corporation Pty Ltd