Solved

simplify query (update with a inner query join)

Posted on 2010-11-10
18
325 Views
Last Modified: 2012-05-10
i have a update using an inner query..

could you make this  more efficient in TSQL?

this may update 100k records at a time.

thanks
/*

INNER QUERY

select distinct a.abc,b.xyz from tbladult a,table_two b

where a.keyPK = b.keyFK and a.abc is not null and b.xyz is not null

*/



update a 

set abc = b.abc

from table_one a

join 

(

/*

INNER QUERY

*/

select distinct a.abc,b.xyz from tbladult a,table_two b

where a.keyPK = b.keyFK and a.abc is not null and b.xyz is not null

)b

on a.xyz = b.xyz and a.abc is null

Open in new window

0
Comment
Question by:anushahanna
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
please ignore the above.. below the correct logic needing review:

update a
set abc = b.abc
from table_one a
join table_two b
on a.keyPK = b.keyFK
join
(
select distinct a.abc,b.xyz from table_one a,table_two b
where a.keyPK = b.keyFK and a.abc is not null and b.xyz is not null
)c
on b.xyz = c.xyz and a.abc is null

abc is only in table_one
xyz is only in table_two
keyPK and keyFK make a one to many relationship between table_one and table_two
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
Hi, just a question

Update A

This A is the same tbladult a ???


Cheers
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
Your second query won't work.  You said:

set abc = b.abc

but b is table_two which doesn't have column abc.
0
 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
Have you created an index on table_one that includes the keyPK and ABC columns, and an index on table_two that includes keyFK and XYZ columns? That will speed up your queries.

Also, look at your usage scenarios. Since you're updating data in your database with other data in your database, it might just be more efficient not do the update at all - all the data is there, and you can get the information you need with the correct select statement. If you build a view on your tables based on a select query similar to the one written for your update statement, and the rows are properly indexed, it might make more sense this way. Because of the large data sets and joins involved, the determination is going to be whether or not you are updating or reading more often, and which case has a higher demand for performance.

Finally, lay out the high level logic for your query. I don't necessarily see a mathematical reduction for your logic (maybe someone else would), but there could be another way to skin your cat.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
raulggonzalez, yes, consider table_one for that.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility

jrb1, thanks - it should be c.abc
update a
set abc = c.abc
from table_one a
join table_two b
on a.keyPK = b.keyFK
join
(
select distinct a.abc,b.xyz from table_one a,table_two b
where a.keyPK = b.keyFK and a.abc is not null and b.xyz is not null
)c
on b.xyz = c.xyz and a.abc is null

abc is only in table_one
xyz is only in table_two
keyPK and keyFK make a one to many relationship between table_one and table_two
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
AngryBinary, thanks - yes, the index is in place. the business logic requires the updates, and will help avoid some other redundancies..

0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
here is a sample data:

keyPK/keyFK      abc       xyz    type
458433      NULL      439014 AD
439014      NULL      NULL GF
347545      3266232377      439014 GD
326437      NULL      439014 GS

xyz is always derived from keyPK.
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
So you have some row in Table1 that you want to update with the value from another row in Table1.  Do you really need to go to table2?  If you have multiple rows with matching XYZ and a different ABC value, which one do you want?

Say your data was:

458433      NULL      439014 AD
439014      NULL      NULL GF
347545      3266232377      439014 GD
347546      9999999999      439014 GD
326437      NULL      439014 GS

Do you want 3266232377 or 9999999999?

But a simpler version without the second table:

update a
set abc = b.abc
from table_one a
inner join table_one b
on a.xyz = b.xyz
and a.keyPK <> b.keyPK
where a.abc is null
and b.abc is not null
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
Could you provide sample data from both tables separately, instead of joined?
0
 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
Also, When you say 'xyz' is derived from keyPK, what is the relationship? Can you spell out the logic in terms of business requirements?

0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
jrb1, we need table_two because xyz is pivotal to choose the right record.. let me explain:

table_one (type makes each record distinct)
keyPK       abc                   type
458433      NULL                  AD
439014      NULL                  GF
347545      3266232377            GD
326437      NULL                  GS

table_two

KetFK      xyz    
458433      439014
439014       NULL  (if xyz is same as KeyFK, it *may* be NULL)
347545      439014
326437      439014

or

KetFK      xyz    
458433      439014
439014       439014
347545      439014
326437      439014

The result we need is in table_one

keyPK       abc                   type
458433      3266232377            AD
439014      3266232377            GF
347545      3266232377            GD
326437      3266232377            GS
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
AngryBinary,
xyz points to the actual entity to whom all the records belong to.

458433, 439014, 347545, 326437 all belong to Joey Smith.

But only one of his record has his account reference # for a particular type. But we want to update it for all his records.

The only way to do is to go to table_two, check the keyfk. his xyz value will 'group' his records together. and this xyz value will be one of his keyPK value.  But every keyFK referenced by xyz are his records.

The way to get the right abc value is to trace back if any of the above IDs have a valid abc value - that is why in the inner query, we filter "abc is not null"- if we even get one value back - then we need to take it and update all of his other records.

thank you-
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
clarification:
keyPK and keyFK make a one to one relationship between table_one and table_two
keyFK and xyz make a one to many relationship in table_two
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
update a
set abc = c.abc
from table1 a
inner join table2 b
on a.keyPK = b.keyFK
inner join table1 c
on isnull(b.xyz,b.keyPK) = c.keyPK
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
hmmm...not quite. still looking
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
Comment Utility
OK, how about this?

update a
set abc = d.abc
from table1 a
inner join table2 b
on a.keyPK = b.keyFK
inner join table2 c
on isnull(b.xyz,b.keyFK) = isnull(c.xyz,c.keyFK)
inner join table1 d
on c.keyFK = d.keyPK
where a.abc is null
and d.abc is not null
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
right on- thanks very much.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now