Hi,

This is something like one of Joe Celko's SQL puzzles. I have
inherited a database with two tables:

persistent_store(
id integer primary key,
name char(20) not null
}

persistent_attribs(
id integer references persistent_store(id),
data blob
)

Table persistent_attribs contains at most one record per specified ID
(relationship is 0..1).

Now I want to merge these tables into one. Does anybody know if it is
possible to copy data from persistent_attribs.data into
persistent_store.data in one UPDATE statement (preferably in SQL92)?

I though about something like:

UPDATE
persistent_store s
SET
s.data = (SELECT data FROM persistent_attribs a WHERE a.id = s.id)

but this does not work. :)

I know how to make this in stored procedure in Firebird, but that's
not what I want (I'm a Firebird addict forced to perform this
operation on PostgreSQL and I do not want to learn any PgSQL DML).

Thanks in advance,
Roman Rokytskyy