Moving changes from one table to another

You just screwed up the database, and now you need to make some surgical repairs to the database.

Or maybe you have data in development and production tables and you want to move some data changes from one to the other.

Basically the problem is that you have 2 tables with the same schema where 1 column has some different values in one table when compared with the other.

You can’t do an update on an inner join statement, but you can create an updatable view containing the fields you need and then run an update statement on it.

A view is updatable if it meets the following conditions:

A view is updatable if:

  • select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION, or DISTINCT clauses. Aggregate functions can be used in a in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
  • select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
  • The FROM clause in the select_statement references at least one table. select_statement must have more than nontabular expressions, which are expressions that are not derived from a table. For example, this view is not updatable:
CREATE VIEW NoTable AS
 SELECT GETDATE() AS CurrentDate,
 @@LANGUAGE AS CurrentLanguage,
 CURRENT_USER AS CurrentUser

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s