Avatar of anushahanna
anushahannaFlag for United States of America asked on

simplify query (update with a inner query join)

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

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
anushahanna

8/22/2022 - Mon
ASKER
anushahanna

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
raulggonzalez

Hi, just a question

Update A

This A is the same tbladult a ???


Cheers
jrb1

Your second query won't work.  You said:

set abc = b.abc

but b is table_two which doesn't have column abc.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
AngryBinary

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.
ASKER
anushahanna

raulggonzalez, yes, consider table_one for that.
ASKER
anushahanna


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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
anushahanna

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

ASKER
anushahanna

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.
jrb1

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
AngryBinary

Could you provide sample data from both tables separately, instead of joined?
AngryBinary

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

ASKER
anushahanna

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
anushahanna

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-
ASKER
anushahanna

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
jrb1

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
jrb1

hmmm...not quite. still looking
ASKER CERTIFIED SOLUTION
jrb1

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
anushahanna

right on- thanks very much.