T-SQL Update Table Using Join
When working with tables in our relational database we are often faced with updating a field in one table that is based on field from another table. So table A is the target table that has the field that you want to update and Table B is the table that has the desired data. If you are new to SQL this tip should help you out.
What to do?
This one is pretty straight forward. However, if you are new to SQL it may seem a bit strange. Take a step back and think about what you are doing. In essence you want to update the field in one table where the first table has a field that is the same as a field in another table. First you need to think of an alias for Table A and also use an alias for Table B. This way you do not have to type out the entire table name. If you are using schema that is different than dbo this can be annoying.
The syntax below is a general format to update a table using a JOIN condition. It is helpful when doing an update of a field in Table A from a field in Table B. There are several variations of this technique that are useful as well. One variation is that you do not need to use a permanent table. In fact you can use this same technique with nothing but temporary tables or table variables as well.
UPDATE TableA
SET TableA.FieldToUpdate = schema1.Field_From_TableB
FROM schema1.TableA TableA
INNER JOIN schema1.TableB TableB ON TableA.ID = TableB.ID
What is going on here? The first line is the target table “TableA”. The second line uses the SET keyword to specify the field that you want to update. In this case you are setting this field equal to the field in TableB that has the data you desire. The third line is the first of the two alias names and contains that FROM clause. Notice that you first use the complete path to the table and then give the table an alias of “TableA”. You do not have to do this if both of your tables are in the dbo schema. The fourth line is where the true power in this technique lies. In line four you are using INNER JOIN. This is saying that for every field where I have a match between two fields update the field that I have stated that I want to update in line two. In this example the two fields that are the same in both tables are the field “ID”. Notice that the join condition makes use of the second table alias which is in turn used in line two.
Well that is pretty much it. After you do a few of these it will become second nature.
About the Author:
I’m Chad E. Downey, a Certified Data Management Professional (CDMP), currently working as an IT consultant in the Indianapolis area. I have worked with data systems in the Manufacturing, Health Care, Financial and Insurance Industry. I can be reached at chadericdowney@gmail.com.
Resolving the “Sheet is invalid Error” in Tableau
I’ve been busy these days putting together data structures for a data warehouse. The product I’m using for the presentation layer is Tableau. After working for about 3 hours on a dashboard I published it to the server, but found that all of the work sheet views did not show in the dashboard views. Indeed I was getting “The sheet is invalid” error. If you are getting this error and…
T-SQL Concatenating Date and Time
This is a technique I’ve used several times in day to day work. It’s straight forward and easy to understand. 1. Declare your variables DECLARE @date Char(8), @starttime CHAR(8), @endtime CHAR(8), @startdt DATETIME, @enddt DATETIME 2. Set your variables SELECT @date = Convert(varchar,GetDate(),110), @starttime = ’16:50:00′, @endtime = ’23:50:00′ SET @startdt = CAST(@date AS DATETIME) + @starttime SET @enddt = CAST(@date AS DATETIME) + @endtime 3. Use @startdt and @enddt…
SQL Server: Replace Invalid Date using ISDATE function
I often run into invalid date formats. Here’s one way to get around it. CASE WHEN ISDATE([Your_Date_Field]) = 1 THEN [Your_Date_Field] ELSE NULL END AS [Your_Date_Field] This code should replace the invalid date with a null. You might also select into a temporary table to find the bad dates as well for cleanup.
SSIS: SSIS Frameworks (Part 1)
SSIS is Microsoft’s Extract, Transfer and Load (ETL) software that has evolved from the previously available technology in SQL Server 2000 called Data Transformation Services (DTS). If you look at the names for objects used at run time the DTS prefix for objects is still very prevalent. Many of us have been using SSIS for years, but without much guidance on how to get the maximum usage from this part of…
SQL Server: Time Saver writing complex joins
I have a few add-in products loaded for my work in SSMS. One of which is SSMS tools. This is a free add-in for SSMS and it works fantastic. However, as well all know we are often faced with writing complex join statements. If I had a dollar for every join statement I’ve ever written over the past years I’d be extremely wealthy. So here’s my tip: If you are…
The Core Components of an SSIS Package
In this post I want to point out the core components that are used to to create an SSIS package. It’s important to not get confused between what is a development environment feature and what that the feature actually provides. For example, the toolbox is not a core component of an SSIS package, but it is a design feature that lets you select various controls to use when designing one…
SQL Server: Using the OPENROWSET function to query Excel data
Whew! A few months ago I was tasked with importing data from that was composed of Sharepoint linked lists that were being used as part of an Excel worksheet for analysis. Part of the problem was that there wasn’t a standard set of columns to work with in the Excel worksheets. This made it difficult to import each row to a table because the rows could have different columns. Because…
Slowly Changing Dimension Wizard Step by Step
When working with analysis services in conjunction with a data warehouse or data mart you typically have one to many dimensions. Dimensions can be thought of as the tables in your warehouse by which you want to slice and dice your fact table information. For example, it is common to have a time dimension so that you can look at the measures in fact tables for a given amount of…
The Business Intelligence Cheat Sheet
Business Intelligence can be thought of as a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information. Thus enabling more effective strategic, tactical, and operational decision-making. A business intelligence solution may consist of all or some of these methodologies, processes, architectures and technologies. Some examples would be data integration, data quality, data warehousing, master data management, metadata repositories, OLAP Cubes, reporting platforms, data…
Add One