CODEWITHSUNDEEP
mysqlsqljoinsql-update
DerStrom8
DerStrom8

Reputation: 1341

Updating individual table from two joined tables (SQL)

A friend of mine just asked me this question and I'm not sure of the answer.

Is it possible to update an individual table that was added to another table using JOIN?

For example, we have P.table_a and P.table_b. table_a and table_b were "joined". Can I update table_a from the returned (joined) result, and have the original table_a reflect this change? Or does the join make a copy of the tables, so that any edits only affect the result of the JOIN? I'm leaning towards the first option, but I am not 100% sure.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your question is a bit unclear. But the general answer is "yes". Here are some mechanisms:

  • Many databases support join directly in the update syntax. In that case, you can just write the update with an explicit join.

  • Many databases support the merge statement. In that case, the merge acts a lot like an update and allows joins.

  • Many databases support triggers on views. In that case, you can write an update trigger on a view. When it is executed, then the trigger can update the view.

  • Some databases support materialized views. These are views (which can have joined tables). When underlying data changes, then the materialized view will change.

There are probably additional methods that I've left out. No doubt, any database you are using supports at least one of these.

EDIT:

Once again, I don't know what you mean. The only formal use I can think of for "virtual table" is a specific mechanism in SQLite to refer to external data sources.

Perhaps all you really want is a view. This is standard and available in basically all databases. The view definition can have joins in it. When the view is executed, the results come from the most recent versions of the underlying tables. There is no "update" involved; the query that defines the view is executed (in some fashion) when the query referencing it is executed.

Upvotes: 2

Related Questions