SQLTeam.com | Weblogs | Forums

Update a table in one row from another table with multiple rows

I try to accomplish the following request. I have table 1 content the result multiple rows.

Table 1
Batch test1 Test2 Test3 DateOn
2501 5.1 Null Null 2021-11-11 13:23:44
2501 Null 6 Null 2021-11-11 14:23:44
2501 3.4 Null Null 2021-10-11 15:23:44 Note Oldest Result test1
2501 Null Null 2 2021-11-11 18:23:44

Table 2 and Table 3 have batch num already and need updates with the result from table 1.

Table 2 all first results then if found more results add it with()
Table 2
Batch test1 Test2 Test3
2501 3.4,(5.1) 6 2

Table 3 only add the oldest result I found.
Table 3
Batch test1 Test2 Test3
2501 3.4 6 2

So you always have 3 tests. You will never have more than 3 tests?

Hi Yosias
Thank you for your time and help. for you question
You will never have more than 3 tests?
No, never have more than 3 tests.

In my real life, I have 60 tests results for each batch. In some cases, we only get part of these 60 tests results, for example, four results. It is because we discharge the batch.

and why do you have 3 tables? also please always provide sample data as follows

declare @macha table(Batch int, 
test1 int,Test2 decimal(3,1) , 
Test3 decimal(3,1), 
DateOn datetime)

insert into @macha
select 2501 , 5.1  , Null , Null , '2021-11-11 13:23:44' union
select 2501 , Null , 6    , Null , '2021-11-11 14:23:44' union
select 2501 , 3.4  , Null , Null , '2021-10-11 15:23:44' union

select 2501 , Null , Null , 2    , '2021-11-11 18:23:44' 

select * From @macha

Which specific version of SQL Server are you on? SQL 2016+ or less than that?

Scott
the version is
Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Yosias,
thank you for the suggestion. I will do it.
table 1 have information
table 2 have values in the varchar
table 3 have values in numeric

If you get up to SP3, you can use STRING_AGG in SQL. That will make this a LOT easier to do.

I'm trying to think of an efficient way to do this without STRING_AGG.

declare @table1 table(Batch int,
test1 decimal(3,1),Test2 decimal(3,1) ,
Test3 decimal(3,1),
DateOn datetime)

declare @table2 table(Batch int,
test1 varchar (200) ,Test2 varchar (200) ,
Test3 varchar (200)
)

declare @table3 table(Batch int,
test1 decimal(3,1),Test2 decimal(3,1) ,
Test3 decimal(3,1)
)

insert into @table1
select 2501 , 5.1 , Null , Null , '2021-11-11 13:23:44' union
select 2501 , Null , 6.2 , Null , '2021-11-11 14:23:44' union
select 2501 , 3.4 , Null , Null , '2021-10-11 15:23:44' union
select 2501 , Null , Null , 2.6 , '2021-11-11 18:23:44'

insert into @table2
select 2501,'3.4,(5.1)','6.2','2.6'

insert into @table3
select 2501,3.4,6.2,2.6

select * From @table1
select * From @table2
select * From @table3

Hi Scott
Unfortunately, I have no control over doing the updates or upgrading the version on the server. I can request it; however, it will take time.

I'm pretty sure this will work, but see how well it performs for you:


IF OBJECT_ID('tempdb.dbo.#table1') IS NOT NULL
    DROP TABLE #table1;
CREATE TABLE #table1 (
    Batch int NOT NULL, 
    test1 decimal(9, 1) NULL,
    Test2 decimal(9, 1) NULL, 
    Test3 decimal(9, 1) NULL, 
    DateOn datetime NOT NULL
    )

INSERT INTO #table1 VALUES
    (2501 , 5.1  , Null , Null , '2021-11-11 13:23:44'),
    (2501 , Null , 6    , Null , '2021-11-11 14:23:44'),
    (2501 , 3.4  , Null , Null , '2021-10-11 15:23:44'),
    (2501 , Null , Null , 2    , '2021-11-11 18:23:44')

--******************************************************************************

IF OBJECT_ID('tempdb.dbo.#tests') IS NOT NULL
    DROP TABLE #tests;
CREATE TABLE #tests (
    Batch int NOT NULL,
    test# int NOT NULL,
    test varchar(500) NOT NULL,
    DateOn datetime NOT NULL,
    PRIMARY KEY ( Batch, test#, DateOn )
    )

INSERT INTO #tests ( Batch, DateOn, test#, test )
    SELECT t1.Batch, t1.DateOn, 
        ca1.test#, 
        test = CASE WHEN ROW_NUMBER() OVER(PARTITION BY t1.Batch, ca1.test# ORDER BY t1.DateOn) = 1 THEN '' ELSE '(' END + 
               CAST(ca1.test AS varchar(500)) + 
               CASE WHEN ROW_NUMBER() OVER(PARTITION BY t1.Batch, ca1.test# ORDER BY t1.DateOn) = 1 THEN '' ELSE ')' END
    FROM #table1 t1
    CROSS APPLY (
        SELECT test# = CASE WHEN test1 IS NOT NULL THEN 1 WHEN test2 IS NOT NULL THEN 2 ELSE 3 END, test = COALESCE(test1, test2, test3)
    ) AS ca1

SELECT * 
FROM #tests

SELECT 
    t1_B.Batch,
    test1 = (SELECT '' + test FROM #tests t1 WHERE t1.Batch = t1_B.Batch AND t1.test# = 1 ORDER BY t1.DateOn FOR XML PATH(''), TYPE).value('.', 'varchar(500)'),
    test2 = (SELECT '' + test FROM #tests t2 WHERE t2.Batch = t1_B.Batch AND t2.test# = 2 ORDER BY t2.DateOn FOR XML PATH(''), TYPE).value('.', 'varchar(500)'),
    test3 = (SELECT '' + test FROM #tests t3 WHERE t3.Batch = t1_B.Batch AND t3.test# = 3 ORDER BY t3.DateOn FOR XML PATH(''), TYPE).value('.', 'varchar(500)')
FROM (
    SELECT DISTINCT Batch
    FROM #table1
) AS t1_B
1 Like

Here solution I working on
declare @table1 table(t1id int IDENTITY(1,1) NOT NULL,Batch int,
test1 decimal(3,1),Test2 decimal(3,1) ,
Test3 decimal(3,1),
DateOn datetime)

declare @table2 table(t2id int IDENTITY(1,1) NOT NULL,Batch int,
test1 varchar (200) ,Test2 varchar (200) ,
Test3 varchar (200)
)

declare @table3 table(t3id int IDENTITY(1,1) NOT NULL,Batch int,
test1 decimal(3,1),Test2 decimal(3,1) ,
Test3 decimal(3,1)
)

insert into @table1
select 2501 , 5.1 , Null , Null , '2021-11-11 13:23:44' union
select 2501 , Null , 6.2 , Null , '2021-11-11 14:23:44' union
select 2501 , 3.4 , Null , Null , '2021-10-11 15:23:44' union
select 2501 , Null , Null , 2.6 , '2021-11-11 18:23:44'

insert into @table2
select 2501,'3.4,(5.1)','6.2','2.6'

insert into @table3
select 2501,Null,Null,Null

select * From @table1
select * From @table2
select * From @table3

DECLARE @CursorID INT = 1;
DECLARE @RunningTotal BIGINT = 0;
DECLARE @RowCnt BIGINT = 0;
DECLARE @Batch varchar(50) = '';
DECLARE @Test1 AS varchar (50)

-- get a count of total rows to process
SELECT @RowCnt = COUNT(0) FROM @table2

Select @RowCnt as Rowc

WHILE @CursorID <= @RowCnt
BEGIN
-- get a PO and Coil I want to udate
Select @Batch = BATCH
FROM @table3
where t3id = @CursorID;

-- Select older test for PO and Coil I want to udate

Select top 1 @test1 = convert(varchar(50), test1)
From
@table1 Sgng

							where     Sgng.test1 IS NOT NULL 
							          and  
									  Sgng.Batch = @Batch
							order by  Sgng.DateOn

UPDATE @table3

Set

test1 = IsNull(Convert( numeric(38, 4), @test1), test1)

where Test1 is NULL
and
Batch = @Batch

SET @RunningTotal += @CursorID

SET @CursorID = @CursorID + 1

SET @test1 = NULL

END
select * From @table3

I will test it will back to you.
I try to get only data we generate on day period it will reduce a lot.
Thank you very much for your help.

Cursors will work, but they'll be pretty slow.

Scott,

Thank you very much for your help and time.