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

LVL 6
anushahannaAsked:
Who is Participating?
 
jrb1Connect With a Mentor Commented:
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
 
anushahannaAuthor Commented:
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
 
raulggonzalezCommented:
Hi, just a question

Update A

This A is the same tbladult a ???


Cheers
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
jrb1Commented:
Your second query won't work.  You said:

set abc = b.abc

but b is table_two which doesn't have column abc.
0
 
AngryBinaryCommented:
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
 
anushahannaAuthor Commented:
raulggonzalez, yes, consider table_one for that.
0
 
anushahannaAuthor Commented:

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
 
anushahannaAuthor Commented:
AngryBinary, thanks - yes, the index is in place. the business logic requires the updates, and will help avoid some other redundancies..

0
 
anushahannaAuthor Commented:
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
 
jrb1Commented:
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
 
AngryBinaryCommented:
Could you provide sample data from both tables separately, instead of joined?
0
 
AngryBinaryCommented:
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
 
anushahannaAuthor Commented:
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
 
anushahannaAuthor Commented:
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
 
anushahannaAuthor Commented:
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
 
jrb1Commented:
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
 
jrb1Commented:
hmmm...not quite. still looking
0
 
anushahannaAuthor Commented:
right on- thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.