Hi Team
I have a scenario as below. I want to update the Chilld value with the PCHILD for the below table. Please let me know how to do it as I have only NAME as common column to join.
any help is appreciated
Hi,
can you explain the rules? I can't figure out a correct rule. Why is Child = 006 in both cases for Name=PQR in the result, whereas before Child and PCHILD seemed to be identical for Name = ABC, XYZ ?
Thanks
For PQR Child 3..The Child 3 is not having any value in PCHILD like 003, so we need to take max value from PCHILD for that parent and need to update.
So you want to update the CHILD with Maximum value from PCHILD for each name ?
Pseudocode:
update yourtable set Child = Max value from PChild
where Child does not have a value
grouped by name , i.e. de the max value by name
If the CHILD is, say, 2 and the PCHILD is 002, they "match" (other than the leading zeros), and the CHILD must become 002.
However, 3 does not match 001. So that CHILD is not updated to 003, and not to 001 either, but it is updated to the max PCHILD for that NAME.
I have use SQL Server 2017 as you did not tell us your version. What version do you use?
Here is an example to check against your requirements. It's not updating any data so you can adapt the table name in the query and check to see if my understanding of your requirements is correct.
--drop only works 2016 onwards
drop table if exists #tmp;
Select * into #tmp
from (Values
('ABC','1','ABC','001')
,('ABC','2','ABC','001')
,('ABC','3','ABC','002')
,('XYZ','1','XYZ','002')
,('XYZ','2','XYZ','002')
,('XYZ','','XYZ','005')
,('PQR','','PQR','001')
,('PQR','','PQR','003')
,('PQR','5','PQR','005')
,('PQR','9','PQR','009')
) t(Name,Child,PName,PChild)
;with prepared_data as (
Select
Name,Child,PName,PChild
,Child_Compare = right ( replicate('00',2)+Child,3)
,PCHILD_MAX = max(PChild) Over (Partition by Name Order by (Select Null))
from #tmp
)
Select Name,Child,PName,PChild
,Child_Updated=case when Child_Compare = PChild then PChild else PCHILD_MAX End
from prepared_data
Here is an example output of the query
You see how the update would happen by comparing Child with Child_Updated.
The key is inside the so called CTE (= temporary view). Here I do two things:
- I add 00 to the Child column and assume it is three characters long
- I create the Max value by usiing a window function
Attention: I assume that PChild Max-Value is "Numeric" i.e. behaves like this. If this is not the case you need more criteria to define the Max value for PChild (for example the latest value which has been updated;
--drop only works 2016 onwards
drop table if exists #tmp_a;
drop table if exists #tmp_b;
Select * into #tmp_a
from (Values
('ABC','1',' ')
,('ABC','2',' ')
,('ABC','3',' ')
,('XYZ','1',' ')
,('XYZ','2',' ')
,('XYZ','',' ')
,('PQR','',' ')
,('PQR','',' ')
,('PQR','5',' ')
,('PQR','9',' ')
) t(Name,Child,New_Child)
Select * into #tmp_b
from (Values
('ABC','001')
,('ABC','002')
,('ABC','003')
,('XYZ','001')
,('XYZ','002')
,('XYZ','')
,('PQR','')
,('PQR','')
,('PQR','005')
,('PQR','009')
) t(PName,PChild)
;with prepared_tmp_a as (
Select name,Child
, Name_id = ROW_NUMBER() Over ( partition by name order by (Select Null)) --no order by as criteria in exmple not clear yet
,Child_Compare = right ( replicate('00',2)+Child,3)
from #tmp_a
), prepared_temp_b as (
Select PName, PChild
, Name_id = ROW_NUMBER() Over ( partition by pname order by (Select Null))
, PCHILD_MAX = max(PChild) Over (Partition by pName Order by (Select Null))
from #tmp_b
)
Select Name,Child,PName,PChild
,Child_Updated=case when Child_Compare = PChild then PChild else PCHILD_MAX End
from prepared_tmp_a inner join prepared_temp_b on prepared_tmp_a.Name = prepared_temp_b.PName and prepared_tmp_a.Name_id = prepared_temp_b.Name_id
It works for this example but you have to understand that the creation of the row_number must guarantee to have the correct order between table_a and table_b .
At the moment it's just a coincidence that it works because the rows are read in order of the the creation. Try to find a criteria that guarantees that "row 1 in your example in table a and table b are always the same". If there is no other criteria you can try and see if SQL Server behaves in a certain way BUT THE SOLUTION IDEA DOES NOT GUARANTEE THIS. That is because the output of a Select statement does not guarantee the same outcome without an Order by clause.
What about the numbers in the child table? Can they be orderd in some way? And table b and PChild? Also what about "Row 1" in your example? How do you know it is this way and not the other way around? What intrinsic knowledge are you using? Can it be explained or put on a piece of paper?
1 Like
--drop table child
--create table child
--(
--Name varchar(50),
--child varchar(50),
--pname varchar(50),
--pchild varchar(50)
--)
--insert into child values('ABC','1','ABC','001')
--insert into child values('ABC','2','ABC','002')
--insert into child values('XYZ','1','XYZ','002')
--insert into child values('XYZ','2','XYZ','002')
--insert into child values('PQR','3','PQR','001')
--insert into child values('PQR','6','PQR','006')
UPDATE ca1
SET ca1.child = ca2.pchild
FROM child ca1
JOIN child ca2 ON (ca1.Name = ca2.Name and ca1.pchild = ca2.pchild)
select * from child