Updating 1 record in Table A with multiple records in Table B

Question

Updating 1 record in Table A with multiple records in Table B

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

kevaburg SSCoach Points: 18131 More actions · Answer 1

John Mitchell-245523 - Friday, September 29, 2017 5:35 AM
You pay your money, you take your choice. You could easily rewrite as follows - I don't think it would make any difference to performance either way.
...Field1 = CASE WHEN Field1 = '' THEN s.MaxField1 ELSE m.Field1 END...
John

Cheers John, I never used COALESCE , only CASE so at least it doesn't look like I have missed anything! 🙂

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 2

John Mitchell-245523 - Friday, September 29, 2017 5:25 AM
TheCTEGuy - Friday, September 29, 2017 4:59 AM
sgmunson - Wednesday, September 13, 2017 12:42 PM
jchatton1 - Wednesday, September 13, 2017 12:32 PM
sgmunson - Monday, September 11, 2017 10:21 AM
jchatton1 - Saturday, September 9, 2017 7:39 AM
m.richardson.home - Saturday, September 9, 2017 2:49 AM
This should do the trick:-
UPDATE m SET     Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),     Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),     Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID)) FROM mstr m
Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes
I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs? In my situation, I don't have NULLs I have blanks or spaces (' '). Will this code still work?
Try this instead:
UPDATE m SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)), Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)), Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID)) FROM mstr AS m
Please note addition of RTRIM function.
Thanks, so far this has worked nicely in testing. I like that COALESCE maintains the order the child values need to be tested.
Glad I could help.   COALESCE always takes the first non-null expression, or if there aren't any non-null values, it returns NULL.   Thus it can be important to understand that the order of the values specified, matters.
EDIT: I just realized you said the order of the child values.   That is controlled by the SELECT TOP(1) that has an ORDER BY clause.
One of the issue with this approach is that COALESCE internally breaks into CASE statement, i.e sub query is being evaluated twice. Below is the snapshot of exec plan. Make sure that the no. of rows are less than a million.
Thanks 🙂
The COALESCE function isn't the reason for the multiple scans - it's the fact that a subquery is used for each column being updated, and that subquery does a scan each time. This code will do only a single scan of the chld table:
UPDATE m SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), s.MaxField1) ,    Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), s.MaxField2) ,    Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), s.MaxField3) FROM mstr AS m JOIN (     SELECT CustID     ,    MAX(Field1) AS MaxField1     ,    MAX(Field2) AS MaxField2     ,    MAX(Field3) AS MaxField3     FROM chld     GROUP BY custid     ) s ON m.CustID = s.CustID
John

Yup u are correct. The same is explained @ microsoft docs. I hope I understood it right 🙂

I got a better plan .. hope this is faster 🙂

First solve the problem then write the code !

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

TheCTEGuy - Friday, September 29, 2017 6:26 AM
Yup u are correct. The same is explained @ microsoft docs. I hope I understood it right 🙂
I got a better plan .. hope this is faster 🙂

Never seen that before. But I don't think it applies when the subquery is the final argument in the COALESCE function, since, as far as I know, it will only be evaluated at all if all the other arguments are NULL.

I got a better plan .. hope this is faster 🙂

Yes, excellent - by removing the GROUP BY, you've eliminated the SORT operation.

John

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 4

John Mitchell-245523 - Friday, September 29, 2017 7:16 AM
TheCTEGuy - Friday, September 29, 2017 6:26 AM
Yup u are correct. The same is explained @ microsoft docs. I hope I understood it right 🙂
I got a better plan .. hope this is faster 🙂
Never seen that before. But I don't think it applies when the subquery is the final argument in the COALESCE function, since, as far as I know, it will only be evaluated at all if all the other arguments are NULL.
I got a better plan .. hope this is faster 🙂
Yes, excellent - by removing the GROUP BY, you've eliminated the SORT operation.
John

Thanks John - I am new to this blog, just trying my best 🙂

First solve the problem then write the code !