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

Transact-SQL
1

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

2

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

3

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.

4

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
5

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

6

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)

7

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

8

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

9

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

10

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

11

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.

12

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
13

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

14

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.

15

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

16

Scott,

Thank you very much for your help and time.