Richard Huxton <dev@archonet.com> writes:
There are two things (AFAICT) you need to be able to do to update (NOTE - not insert) a view.
1. Identify the underlying table(s) for the updated column(s)
2. Identify (primary) key values for the table(s) being updated.
So - I could have a join listing users and how many email aliases they have (so sum()) and still update their name, so long as the key for the users table was present in the view.
No; you'd also have to have some guarantee that a given underlying table row gives rise to at most one join row. If the same table row gives rise to multiple join rows, then a request specifying an UPDATE of just one of those join rows can't be satisfied.
But you can't specify an update of a single row, only those where certain values match. Say you have a view "user_email_vw" with the following columns (from obvious tables):
user_email_vw: u_id, u_name, e_id, e_address
Updating the view "WHERE u_id=123" may well update more than one row (where a user has multiple emails), but that's exactly equivalent to updating the user-table "WHERE u_name = 'John Smith'". In the view (u_id) is not a key any more.
If you have a many-many relationship, (say worker<=>department) then again you may update multiple rows in the view ("WHERE dept_id=123"), but so what - that's what you asked to do.
I'm not saying this is always the behaviour you'd want. Imagine an address table where you have a country-code field and a lookup table of countries. I almost certainly DON'T want the lookup table updated via the view, but there's no easy solution to that - it's to do with the semantics of the join, not its syntax.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
