September 8, 2017 at 7:10 pm
Hello. SQL Server 2012, novice level programmer. I am trying to UPDATE a master table with records from a daily transaction table using this common SQL construct:
UPDATE
SET
Field1 = CASE WHEN s1.Field1 < s2.Field1 THEN s2.Field1 ELSE s1.Field1 END
Field2 = CASE WHEN s1.Field2 = '' THEN s2.Field2 ELSE s1.Field2 END
Field3 = CASE WHEN s1.Field3 >= s2.Field3 THEN s2.Field3 ELSE s1.Field3 END
...
from dbo.Table1 s1 INNER JOIN dbo.Table2 s2
on s1.CustID = s2.CustID
The table have the same columns. The problem I am having is that there is a one to zero-or-more relationship between Table1 and Table2, and the Table1 records are not showing any update values from any records from Table2 that are the 2nd, 3rd, 4th, etc. record for that CustID. If there are no records or just 1 record in Table2 for a given CustID in Table1, it works as expected. Am I missing something or doing something wrong? Can this approach work in this situation?
September 8, 2017 at 7:56 pm
Please give some test data and expected output.
Based on current input you are trying to update parent table (Table1) with child table (Table2). Why do you want to use a generic query, if need to update with latest record from table2 then just pick those records using a subquery. If need max value then pick that only and join. just don't join generic and try to update, SQL will update with first record it will match.
September 8, 2017 at 10:40 pm
Here is an example of what I am trying to do.
My beginning master table:RowID CustID RecordID Field1 Field2 Field3
-------------------------------------------------------
12655 38 1 K3390
66844 165 1 2885
My child tableRowID CustID RecordID Field1 Field2 Field3
------------------------------------------------------
130 38 1 P3477
178 38 2 4029
183 38 3 RS538 4272
184 38 4 2907 5TRJ
207 165 1 6778G
311 165 2 L3488
318 165 3 BY23J2 5344
My desired outcome for the master table:RowID CustID RecordID Field1 Field2 Field3
----------------------------------------------------
12655 38 1 K3390 4272 4029
66844 165 1 BY23J2 L3488 2885
In this example, the goal is to essentially fill in each blank cell in the master with the first non-blank value from the child table.
In real life, there are a few fields with boolean comparisons between s1 and s2, but I want to take one step at a time.
There are 60 attributes with over 1 million child records. Most fields are VARCHAR but there are some INT as well.
September 8, 2017 at 11:16 pm
Welcome to SSC. It helps everyone if you post CREATE TABLE and INSERT scripts so people can run them and have at least a mockup of what you're dealing with. That way you get tested answers instead of guesses or no answer... so here's how to post your scripts:CREATE TABLE mstr (
ID INT,
CustID INT PRIMARY KEY,
RecordID TINYINT,
Field1 CHAR(5),
Field2 CHAR(5),
Field3 CHAR(5)
);
GO
CREATE TABLE chld (
RowID INT PRIMARY KEY,
CustID INT,
RecordID TINYINT,
Field1 CHAR(5),
Field2 CHAR(5),
Field3 CHAR(5)
CONSTRAINT fkCustID FOREIGN KEY (CustID) REFERENCES mstr(CustID));
GO
INSERT INTO mstr (ID, CustID, RecordID, Field1, Field2, Field3) VALUES
(12655, 38, 1, 'K3390', NULL, NULL),
(66844, 165, 1, NULL, NULL, 2885);
INSERT INTO chld (RowID, CustID, RecordID, Field1, Field2, Field3)
VALUES (130, 38, 1, 'P3477', NULL, NULL)
,(178, 38, 2, NULL, NULL, '4029')
,(183, 38, 3, 'RS538', '4272',NULL)
,(184,38,4,NULL,'2907','5TRJ')
,(207,165,1,NULL,NULL,'6778G')
,(311,165,2,NULL,'L3488',NULL)
,(318,165,3,'BY232',NULL,'5344');
September 9, 2017 at 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
September 9, 2017 at 7:35 am
pietlinden - Friday, September 8, 2017 11:16 PMWelcome to SSC. It helps everyone if you post CREATE TABLE and INSERT scripts so people can run them and have at least a mockup of what you're dealing with. That way you get tested answers instead of guesses or no answer... so here's how to post your scripts:CREATE TABLE mstr (
ID INT,
CustID INT PRIMARY KEY,
RecordID TINYINT,
Field1 CHAR(5),
Field2 CHAR(5),
Field3 CHAR(5)
);
GO
CREATE TABLE chld (
RowID INT PRIMARY KEY,
CustID INT,
RecordID TINYINT,
Field1 CHAR(5),
Field2 CHAR(5),
Field3 CHAR(5)
CONSTRAINT fkCustID FOREIGN KEY (CustID) REFERENCES mstr(CustID));
GOINSERT INTO mstr (ID, CustID, RecordID, Field1, Field2, Field3) VALUES
(12655, 38, 1, 'K3390', NULL, NULL),
(66844, 165, 1, NULL, NULL, 2885);INSERT INTO chld (RowID, CustID, RecordID, Field1, Field2, Field3)
VALUES (130, 38, 1, 'P3477', NULL, NULL)
,(178, 38, 2, NULL, NULL, '4029')
,(183, 38, 3, 'RS538', '4272',NULL)
,(184,38,4,NULL,'2907','5TRJ')
,(207,165,1,NULL,NULL,'6778G')
,(311,165,2,NULL,'L3488',NULL)
,(318,165,3,'BY232',NULL,'5344');
Thanks for the suggestion, I will do this going forward.
September 9, 2017 at 7:39 am
m.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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?
September 11, 2017 at 10:21 am
jchatton1 - Saturday, September 9, 2017 7:39 AMm.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 13, 2017 at 12:32 pm
sgmunson - Monday, September 11, 2017 10:21 AMjchatton1 - Saturday, September 9, 2017 7:39 AMm.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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 mPlease 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.
September 13, 2017 at 12:42 pm
jchatton1 - Wednesday, September 13, 2017 12:32 PMsgmunson - Monday, September 11, 2017 10:21 AMjchatton1 - Saturday, September 9, 2017 7:39 AMm.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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 mPlease 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2017 at 10:44 am
Would an ON UPDATE CASCADE be a viable option here?
September 29, 2017 at 4:59 am
sgmunson - Wednesday, September 13, 2017 12:42 PMjchatton1 - Wednesday, September 13, 2017 12:32 PMsgmunson - Monday, September 11, 2017 10:21 AMjchatton1 - Saturday, September 9, 2017 7:39 AMm.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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 mPlease 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 🙂
First solve the problem then write the code !
September 29, 2017 at 5:25 am
TheCTEGuy - Friday, September 29, 2017 4:59 AMsgmunson - Wednesday, September 13, 2017 12:42 PMjchatton1 - Wednesday, September 13, 2017 12:32 PMsgmunson - Monday, September 11, 2017 10:21 AMjchatton1 - Saturday, September 9, 2017 7:39 AMm.richardson.home - Saturday, September 9, 2017 2:49 AMThis 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 mColumn 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 mPlease 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
September 29, 2017 at 5:27 am
Maybe a little bit off track but is there a reason why a COALESCE would be used instead of a CASE statement?
September 29, 2017 at 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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply