SQL tuning : Updating a column by joining 4 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL tuning : Updating a column by joining 4 tables

Author

Topic

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-16 : 13:17:31

Hi,

I have to update a target table column by joining 4 tables

Table 1 : Job_ID, Job_name --> has only 20 records
Table 2 : company_ID,Job_name
Table 3 : company_ID,line_ID --> has many records for each company_ID around 8 lakhs.
Table 4(Target Table) : line_ID,status_flag

Table 1 and Table 2 has 1-2-1 relationship
Table 2 and Table 3 has 1-2-many relationship
Table 3 and Table 4 has 1-2-1 relationship

I want to update the status_flag with 'Y' in table 4. Below is the query which I have written please suggest if this can be optimized.

update a
set d.status_flag = 'Y'
from
table_4 a
JOIN
table_3 b
on a.line_id = b.line_id
JOIN
table_2 c
on c.company_id = b.company_id
JOIN
table_1 d
d.job_name = c.job_name

Please suggest if this can be optimized.

Thanks for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-16 : 13:34:49

Ensure that each of the join conditions is indexed and then check the execution plan.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-16 : 14:39:49

Thanks Tara!

Yes, the join condition is indexed. So, is the query correctly optimized or there's any chance of improvements.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-16 : 14:42:38

The query is very simple. As long as EACH of the columns in the various joins are indexed, then it should be efficient. You'll need to verify the execution plan.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-17 : 01:13:43

Thanks Tara!

will check the explain plan.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-02 : 12:04:29

Something must be missing here. Why join at all if there are no conditions??

update table4
set status_flag = 'Y'

Rahul Raj
Starting Member

41 Posts

Posted - 2015-05-23 : 18:08:19

Hi Scott/Tara,

Thanks for your response. I forget to mention that there's a 'Date' filter condition in Table 1. Also, the relationship is as below :

Table 1 and Table 2 has 1-2-1 relationship
Table 2 and Table 3 has 1-2-many relationship
Table 3 and Table 4 has 1-2-1 relationship

update a
set d.status_flag = 'Y'
from
table_4 a
JOIN
table_3 b
on a.line_id = b.line_id
JOIN
table_2 c
on c.company_id = b.company_id
JOIN
table_1 d
d.job_name = c.job_name
where d.start_date < 'some date value'

After execution, I am getting some more records updated in Table 4 than expected. I suspect the join between table 3 and 4 is not correct e.g if the data is as below

Table 3

Company-ID line-ID
111 1
111 2
111 3
222 4
222 5
333 6
333 7

Table 4

line-ID status-Flag
1 Y
2 Y
3 Y
4 Y
5 Y
6 N
7 N

so, if the company ID eligible are 111 and 222; so I want only line-id's 1,2,3 and 4,5 to be updated; but when i checked the count of records updated in table 4 then it's showing me more than 5 record in Table 4.

Can someone please suggest me the query how I can identify the additional records which are updated in table 4 and how i can identify the corresponding records in Table 1.

Your help is much appreciated!!
Thanks in advance

Rahul Raj
Starting Member

41 Posts

Posted - 2015-05-24 : 23:24:53

hi,
Can someone please suggest!

many Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources