CODEWITHSUNDEEP
sql-servert-sql
Riskworks
Riskworks

Reputation: 241

TSQL Update query

I have data that looks like this:

Seq PairSeq LineType    Aline   Bline
1   451     L           -3      3
2   451     O           NULL    NULL
3   453     O           NULL    NULL
4   453     L           2.5     -2.5

I need to simply update the NULLs with the values from the row sharing the same pair seq. I have tried to do this with a loop. Can anyone suggest a efficient way to accomplish this update? Thanks in advance.

Upvotes: 0

Views: 71

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

    CREATE TABLE #tt(seq INT,PairSeq INT,LineType VARCHAR(100),Aline FLOAT,Bline FLOAT)
    INSERT INTO #tt
    SELECT 1,451,'L',-3,3 UNION
    SELECT 2,451,'O',NULL,NULL UNION
    SELECT 3,453,'O',NULL,NULL UNION
    SELECT 4,453,'L',2.5,-2.5 

    UPDATE t SET t.Aline=o.Aline,t.Bline=o.Bline FROM #tt AS t
    INNER JOIN #tt AS o ON t.PairSeq=o.PairSeq AND o.Aline IS NOT NULL AND o.Bline IS NOT NULL
    WHERE t.Aline IS NULL OR t.Bline IS NOT NULL

Upvotes: 0

McNets
McNets

Reputation: 10807

Try this:

UPDATE YourTable
SET Aline = T2.Aline, Bline = T2.Bline
FROM YourTable T1
     INNER JOIN (SELECT Seq, PairSeq, Aline, Bline
                 FROM YourTable
                 WHERE Aline IS NOT NULL and Bline IS NOT NULL) T2
     ON T1.PairSeq = T2.PairSeq
WHERE T1.Aline IS NULL AND T1.Bline IS NULL;

Upvotes: 2

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

You shoyld use a subquery or a cte. When using sql, you should avoid using loops.

I am typing frim my phone, so not easy but if only one value should be modified, you can do it with a subquery. For multuple values i would suggest using a cte.

Update table Set aline = ( select x.aline from table x where aline is not null and x.seq = table.seq ) and aline is not null

Upvotes: 0

Related Questions