SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO

Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement

There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them.

Let us see the questions here.

  • I want to delete from multiple table in a single statement how will I do it?
  • I want to update multiple table in a single statement how will I do it?

The answer is – No, You cannot and you should not.

SQL Server does not support deleting or updating from two tables in a single update. If you want to delete or update two different tables – you may want to write two different delete or update statements for it. This method has many issues – from the consistency of the data to SQL syntax.

Now here is the real reason for this blog post – yesterday I was asked this question again and I replied my canned answer saying it is not possible and it should not be any way implemented that day. In the response to my reply I was pointed out to my own blog post where user suggested that I had previously mentioned this is possible and with demo example. Let us go over my conversation – you may find it interesting. Let us call the user DJ.

DJ: Pinal, can we delete multiple table in a single statement or with single delete statement?
Pinal: No, you cannot and you should not.
DJ: Oh okey, if that is the case, why do you suggest to do that?
Pinal: (baffled) I am not suggesting that. I am rather suggesting that it is not possible and it should not be possible.
DJ: Hmm… but in that case why did you blog about it earlier?
Pinal: (What?) No, I did not. I am pretty confident.
DJ: Well, I am confident as well. You did.
Pinal: In that case, it is my word against your word. Isn’t it?
DJ: I have proof. Do you want to see it that you suggest it is possible?
Pinal: Yes, I will be delighted too.
(After 10 Minutes)
DJ: Here are not one but two of your blog posts which talks about it -

Pinal: Oh!
DJ: I know I was correct.
Pinal: Well, oh man, I did not mean there what you mean here.
DJ: I did not understand can you explain it further.
Pinal: Here we go.

The example in the other blog is the example of the cascading delete or cascading update. I think you may want to understand the concept of the foreign keys and cascading update/delete. The concept of cascading exists to maintain data integrity. If there primary keys get deleted the update or delete reflects on the foreign key table to maintain the key integrity and data consistency. SQL Server follows ANSI Entry SQL with regard to referential integrity between PrimaryKey and ForeignKey columns which requires the inserting, updating, and deleting of data in related tables to be restricted to values that preserve the integrity. This is all together different concept than deleting multiple values in a single statement.

When I hear that someone wants to delete or update multiple table in a single statement what I assume is something very similar to following.

DELETE/UPDATE Table 1 (cols) Table 2 (cols)
VALUES … which is not valid statement/syntax as well it is not ASNI standards as well.

I guess, after this discussion with DJ, I realize I need to do a blog post so I can add the link to this blog post in my canned answer. Well, it was a fun conversation with DJ and I hope it the message is very clear now.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – INNER JOIN Returning More Records than Exists in Table

I blog and engage with the community because it gives me satisfaction when someone resolves an issue. A few days ago, I blogged about a DBA who began his first day at a new company and could not find out where the installation summary file was. He was very happy when I featured his story on our blog. Today he asked me another question and when I received his question my first reaction was – not possible. Later I said, may be possible, and when he shared more information, I said of course it is possible and natural. Let us go over these conversations in the exact order of how they occurred.

(I have modified the emails to chat format as it is easier to understand)

Jeff: Pinal, help needed. My query is returning more data than I need.
Pinal: Well, check your WHERE clause, JOINS etc.
Jeff: The problem is that INNER JOIN is returning more records than exist in the table.
Pinal: Not possible. How can a query return data that does not exist. Are you sure that you are connected to the correct database and running the query on the same server where you are validating the data?
Jeff: Yes, yes, everything is in the right place. Can Inner Join ever return more data than exists in the table?
Pinal: Well, maybe. Here is the story. Inner Join can for sure return more records than exists in the table, but it cannot return records that do not exist. Does that make sense?
Jeff: No.
Pinal: Okay, in simple words, if your table has three rows (values 1, 2, 3), your inner join can return 10 rows but it cannot return you the value 4 as part of the result.
Jeff: In simple English please…
Pinal: Honestly, I cannot make it more simple than what I said just now. Here is the example for you.
(Jeff goes through example and after few minutes)
Jeff: Exactly, I had no equal condition which did this. You helped me again.
Pinal: You’re welcome!
(After few minutes…)
Jeff: Pinal, help needed…

Well, this goes on. Anyway, here is the script that I sent him.

Let us create a scenario. We will create three sample tables and insert values in it.

USE TempDB
GO
-- Sample Table
CREATE TABLE FirstTable (Col1 INT)
CREATE TABLE SecondTable (Col1 INT)
CREATE TABLE ThirdTable (Col1 INT)
GO
INSERT INTO FirstTable (Col1)
VALUES (1), (2), (3), (NULL)
GO
INSERT INTO SecondTable (Col1)
VALUES (1), (2), (3), (NULL)
GO
INSERT INTO ThirdTable (Col1)
VALUES (2), (2), (2), (2), (2), (NULL)
GO

Here you will see the normal use of INNER JOIN where a query returns the results as the number of rows or fewer than the number of rows in the table. This happens because the data is unique in both of the tables.

Query 1: Inner Join with Equal to Sign

-- Query 1
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN SecondTable s ON s.Col1 = f.col1
GO

Query 2: Inner Join with Not Equal to Sign

Here is a query where the data is still unique (distinct) among tables but the result set contains more records than the numbers of rows the table contains. The condition <> (not equal to) returns the result which was excluded in the query where = (equal to) condition was used.

-- Query 2
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN SecondTable s ON s.Col1 <> f.col1
GO

Note: If you combine the data of Query 1 and Query 2, you will get the same result as CROSS JOIN of both the tables. If you want to understand further, read my earlier article Introduction to Join – Visual Explanation.

Query 3: Inner Join with Non-Distinct Row Table

Here is a query where data in another table is not unique and INNER JOIN returns more records than one of the tables.

-- Query 3
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN ThirdTable s ON s.Col1 = f.col1
GO

Summary

Inner Join can for sure return more records than the records of the table. Inner join returns the results based on the condition specified in the JOIN condition. If there are more rows that satisfy the condition (as seen in query 2), it will return you more results. Reference: Introduction to Join – Visual Explanation.

Well, feel free to send your questions or even solutions. If I find them interesting I will share them with everybody with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Quick Note about JOIN – Common Questions and Simple Answers

This blog post is written in response to the T-SQL Tuesday post of JOIN. This is a very interesting subject. Years ago, I wrote my article about SQL SERVER – Introduction to JOINs – Basic of JOINs, ‑ till date, it is my most favorite article on the blog.

Today we are going to talk about join and lots of things related to the JOIN. I recently started office hours to answer questions and issues of the community. I receive so many questions that are related to JOIN. I will share few of the same over here. Most of them are basic, but note that the basics are of great importance.

Without further ado, let me continue with the question and answers.

Q: Which one of the following is a better method?
Method 1:
SELECT t1.*, t2.*
FROM t1,t2
WHERE t1.col1 = t2.col1

Method 2:
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

A: The answer to this question will bring some interesting conversation. I strongly prefer method 2 because it is much cleaner to understand and if I have to use table level hints or so on, it is much convenient to do the same. I would suggest going ahead with method 2. Currently with regard to performance and execution plan, both the methods show the same (most of the time). However, with respect to standard and future innovation, method 2 is the way to go.

When I have to perform a performance tuning task, and if I see method 1, I usually ask the developer to convert it to method 2 as I feel much more comfortable with method 2. Additionally, when you have to work with OUTER JOIN, you will have to do so.

Q: What is better ‑ subquery or JOIN?
Subquery:
SELECT t1.*
FROM t1
WHERE t1.col1 IN (SELECT t2.col1 FROM t2)

Join:
SELECT t1.*
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

In this case, there is no right answer. You should use the one that gives you optimal performance. I have seen cases when the subquery gives optimal performance as well join giving optimal performance when compared to each other. I have seen either of them performing so well that I think one has to test out both the methods before selecting one. If you are facing situation where you are not sure which method you should select, I suggest that you go with your intuition. I still prefer JOIN over any other method, but in this case, I will suggest you to test your options.

Q: How to simulate Join?
A: I get this question a lot of times, and I have no answer. Here, I want your help as I do not even understand this question.

Q: How can I change my LEFT JOIN to RIGHT JOIN and get the same answer?
A: Sure. Here is quick example of the same:

Left Join:
SELECT t1.col1, t2.col2
FROM t1 LEFT JOIN t2 ON ON t1.col1 = t2.col1

Right Join:
SELECT t1.col1, t2.col2
FROM t2 RIGHT JOIN t1 ON ON t1.col1 = t2.col1

Both of the above options will give you same result. However, the real question is why you want to do that. What is the reason that you want to change the left join to right join?

Q: Does it matter how I write tables in my join if I am using INNER JOIN only?
A: No it does not matter in case of INNER JOIN as the result will be the same, and the SQL Server Engine will figure out the optimal execution plans for your query. As your question clearly suggests that for any other kind of join (i.e., OUTER JOIN, CROSS JOIN), it will matter for sure. Additionally, there are cases with INNER JOIN ‑ when order is forced on them, they have shown a little performance enhancement. Here is a quick example of the same.

If you have attended my session of Virtual Tech Days few days ago, you would have seen the example of the how forceorder hint works.

Q: Is there a quick tutorial to Joins?
A: I have written an article on this subject earlier, and as I said earlier in this article, I personally like the same a lot. Here you can read about the same: Introduction to JOINs – Basic of JOINs.

Q: Is there any book available to learn T-SQL, which explains various concepts like this easily?
A: I am bit biased but you can read about my books over here.

Q: Is SELF JOIN is a type of INNER JOIN or OUTER JOIN?
A: In fact, it is both an inner as well as outer join. Self Join is a very interesting subject. Here is an interesting article that I have written earlier on this subject: SQL SERVER – The Self Join – Inner Join and Outer Join .

Q: In case of the OUTER JOIN, where should I put the condition?
A: This question requires a detailed answer, I have written a detailed blog post on this subject over here: How ON Clause Effects Resultset in LEFT JOIN .

Q: What is Optimal LEFT JOIN or NOT IN?
A: I personally prefer LEFT JOIN as I have seen LEFT JOIN doing better in many cases. Once again, I suggest you should test it with your query. Here is a quick example of the same: Differences Between Left Join and Left Outer Join.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER- Differences Between Left Join and Left Outer Join

There are a few questions that I had decided not to discuss on this blog because I think they are very simple and many of us know it. Many times, I even receive not-so positive notes from several readers when I am writing something simple. However, assuming that we know all and beginners should know everything is not the right attitude.

Since day 1, I have been keeping a small journal regarding questions that I receive in this blog. There are around 200+ questions I receive every day through emails, comments and occasional phone calls. Yesterday, I received a comment with the following question:

What are the differences between Left Join and Left Outer Join? Click here to read original comment.

This question has triggered the threshold of receiving the same question repeatedly. Here is the answer:

There is absolutely no difference between LEFT JOIN and LEFT OUTER JOIN. The same is true for RIGHT JOIN and RIGHT OUTER JOIN. When you use LEFT JOIN keyword in SQL Server, it means LEFT OUTER JOIN only.

I have already written in-depth visual diagram discussing the JOINs. I encourage all of you to read the article for further understanding of the JOINs:

Read Introduction to JOINs – Basic of JOINs

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Statistics and Best Practices – Virtual Tech Days – Nov 22, 2010

I am honored that I have been invited to speak at Virtual TechDays on Nov 22, 2010 by Microsoft. I will be speaking on my favorite subject of Statistics and Best Practices.

This exclusive online event will have 80 deep technical sessions across 3 days – and, attendance is completely FREE. There are dedicated tracks for Architects, Software Developers/Project Managers, Infrastructure Managers/Professionals and Enterprise Developers. So, REGISTER for this exclusive online event TODAY.

Statistics and Best Practices
Timing: 11:45am-12:45pm
Statistics are a key part of getting solid performance. In this session we will go over the basics of the statistics and various best practices related to Statistics. We will go over various frequently asked questions like a) when to update statistics, b) different between sync and async update of statistics c) best method to update statistics d) optimal interval of updating statistics. We will also discuss the pros and cons of the statistics update. This session is for all of you – whether you’re a DBA or developer!

You can register for this event over here.

If you have never attended my session on this subject I strongly suggest that you attend the event as this is going to be very interesting conversation between us. If you have attended this session earlier, this will contain few new information which will for sure interesting to share with all.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
Previously, I wrote an article about SQL SERVER – The Self Join – Inner Join and Outer Join, and that blog post seems very popular because of its interesting points. It is quite common to think that Self Join is also only Inner Join, but the reality is that it can be anything. The concept of Self Join is very useful that we use it quite often in our coding. However, this is not allowed in the Index View. I will be using the same example  that I have created earlier for the said article.

Let us first create the same table for an employee. One of the columns in this table contains the ID of the manger, who is an employee of that company, at the same time. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need to use Self Join.

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

We will now utilize Inner Join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create View on the table. This will allow well construction of the View without any issues associated with it.

-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create a Clustered Index on the View.

-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO

Unfortunately, the above attempt will not allow you to create the Clustered Index, as evidenced by an error message. It will throw following error suggesting that SELF JOIN is now allowed in the table.

Msg 1947, Level 16, State 1, Line 2
Cannot create index on view “tempdb.dbo.myJoinView”. The view contains a self join on “tempdb.dbo.Employee”.

The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.

If any of you has a better explanation of this subject, please post it here through your comments, and I will publish it with due credit.

The complete script for the example is given below:

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO
/*
Msg 1947, Level 16, State 1, Line 2
Cannot create index on view "tempdb.dbo.myJoinView". The view contains a self join on "tempdb.dbo.Employee".
*/
-- Clean up
DROP VIEW myJoinView
DROP TABLE Employee
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)