CODEWITHSUNDEEP
sqlsql-serverjoinsql-update
Kaptah
Kaptah

Reputation: 9891

Update using Join and selecting newest value

I have following two tables :

ALL_Data

id  |  Name      |   DateAsInteger
-------------------------------------
10  |   gazoline |   20151230
20  |   diesel   |   20160101
30  |   accu     |   20151128
30  |   battery  |   20170307

I know, not the best idea to store dates as integers but can't change it now. 

PIVOT_Data

id  |  SomeInfo     |  Name         
------------------------------------
10  |   xx          |               
20  |   yy          |               
30  |   zz          |

I need to update PIVOT_Data table with newest Name from ALL_Data.

Result: 

id  |  SomeInfo     |  Name         |   newest value    DateAsInteger in ALL_Data
-------------------------------------                   -------------
10  |   xx          |   gazoline    |       <->         20151230
20  |   yy          |   diesel      |       <->         20160101
30  |   zz          |   battery     |       <->         20170307

What I tried : 

UPDATE a
    SET a.Name = b.Name
    FROM PIVOT_Data a 
        join ALL_Data b 
        ON   a.id = b.id
        WHERE b.DateAsInteger = (
            SELECT z1.DateAsInteger
                FROM ALL_Data z1 LEFT JOIN ALL_Data z2
                 ON (z1.id = z2.id AND z1.Ti_DateAsInteger< z2.Ti_DateAsInteger)
    WHERE z2.Ti_DateAsIntegerIS NULL);

Error message:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

What Am I doing? Am I doing something stupid ow wrong?

Should I GROUP BY? If yes, then how?

Any help is highly appreciated. Thank's in advance !

Upvotes: 2

Views: 78

Answers (2)

mohan111
mohan111

Reputation: 8865

using MAX condition

sample data 

DECLARE @ALL_Data TABLE 
    (id int, Name varchar(8), DateAsInteger int)
;

INSERT INTO @ALL_Data
    (id, Name, DateAsInteger)
VALUES
    (10, 'gazoline', 20151230),
    (20, 'diesel', 20160101),
    (30, 'accu', 20151128),
    (30, 'battery', 20170307)
;

Declare @PIVOT_Data TABLE 
    (id int, Info varchar(2),Name varchar(20))
;

INSERT INTO @PIVOT_Data
    (id, Info,name)
VALUES
    (10, 'xx',''),
    (20, 'yy',''),
    (30, 'zz','')
;

Sample Script 

;with CTE AS (Select 
P.id,
P.Info,
MAX(A.DateAsInteger)DateAsInteger 
from 
    @ALL_Data A
    INNER JOIN @PIVOT_Data P
    ON P.id = A.id
GROUP BY P.id,P.Info)


UPDATE P 
SET Name = DateAsInteger 
from CTE 
C INNER JOIN @PIVOT_Data P 
ON P.id = C.id 

Select * from @PIVOT_Data

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Try the below query,

UPDATE  PD
SET     PD.Name =   (   SELECT  TOP 1 AD.Name
                        FROM    ALL_Data    AS  AD
                        WHERE   AD.id   =   PD.id
                        ORDER BY DateAsInteger DESC
                    )
FROM    PIVOT_Data  AS  PD

Upvotes: 1

Related Questions