Start Updating one column table another column

Updating one column table another column

So, we have data coming from: a) table in the same schema/same database, b) tables in the same database but a different schema, c) tables in a separate database on the same SQL Server instance, and d) a view from a database in a separate SQL Server instance all together.

the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update: a b c d ———————————— 1 x y z 2 a1 b1 c1 3 t x z DB2 LUW: –Same as Oracle– UPDATE TABLEA SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 40); Results after the update: a b c d ———————————— 1 x y z 2 a1 b1 c1 3 t x z NOTE: It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon.

– all the normal T-SQL operations you would be able to do including inserting it into the summary table.

What you do with that data-set is entirely up to you – use a CASE statement over it, do aggregation over it, dump it into a temporary table/table variable for further processing etc.

If we now extend it to include the data from (b) as well, the above SQL would change to: INSERT INTO Emp Summary ( As can be seen from above, we have assumed that the select privileges are present and you would be able to join the tables easily then.

Now, if we have to include (c) scenario as well and assuming that we have select privileges in place already on that object, we can either create views/synonyms for those objects in our schema or simply refer to those objects if the permissions are available using a three part naming convention: FROM Analysis.

A calculated column can include a cell that has a different formula from the rest.

This creates an exception that will be clearly marked in the table.

In that post, we had covered how you can update the data in a table with the data from another table. Since it resides in a database on a separate instance, we can first create a linked server to it first and then either reference it using a couple of ways as was shown in that blog post on linked server.

Doing an insert into a table with data from other tables (within the same schema or separate schemas or separate databases or separate databases on separate instances) is also fairly simple. Let’s consider this hypothetical scenario to illustrate this (using SQL Server lingo though the same approach is available in Oracle and DB2 as well) – Say the summary table “tbl Emp Summary” that this reader wanted to insert the data into resides in a database called EXECUTIVE in a schema called ANALYSIS. Or we can simply create a synonym for it and use it.

into This entry was posted on January 27, 2011 at pm and is filed under DB2 LUW, Oracle, SQL Server.