Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

simplify query (update with a inner query join)

Posted on 2010-11-10
18
Medium Priority
?
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 6

Author Comment

by:anushahanna
ID: 34103640
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
ID: 34103874
Hi, just a question

Update A

This A is the same tbladult a ???


Cheers
0
 
LVL 25

Expert Comment

by:jrb1
ID: 34103996
Your second query won't work.  You said:

set abc = b.abc

but b is table_two which doesn't have column abc.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:AngryBinary
ID: 34104190
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
ID: 34104499
raulggonzalez, yes, consider table_one for that.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34104500

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
ID: 34104536
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
ID: 34104577
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
ID: 34104822
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
 
LVL 13

Expert Comment

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

Expert Comment

by:AngryBinary
ID: 34104949
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
ID: 34107821
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
ID: 34107851
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
ID: 34108661
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
ID: 34112855
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
ID: 34112864
hmmm...not quite. still looking
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 2000 total points
ID: 34113029
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
ID: 34121880
right on- thanks very much.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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