Update values like Vlookup

Question

Update values like Vlookup

Dhruvesh Shah

SSCrazy

Points: 2303
More actions
November 12, 2013 at 6:59 pm

#395808

Hi,
I have a table as per below.
CREATE TABLE #TEMP
(
ID NVARCHAR(200) NOT NULL,
SIMNO NVARCHAR(200) NULL,
IMEI NVARCHAR(200) NULL
)
iNSERT INTO #TEMP VALUES ('0412345678','0412345678','013275009174916')
iNSERT INTO #TEMP VALUES ('013275009174916','0412345678','')
iNSERT INTO #TEMP VALUES ('013568258264650','0412345678','')
SELECT * FROM #TEMP
-- should look ilke
ID SIMNO IMEI
0132750091749160412345678
0135682582646500412345678
0412345678 0412345678 013275009174916
I want to update values in IMEI column if SIMNo matches.
I think it's self join but couldn't come up with any solution...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Dhruvesh Shah (11/12/2013)
Hi,
I have a table as per below.
CREATE TABLE #TEMP
(
ID NVARCHAR(200) NOT NULL,
SIMNO NVARCHAR(200) NULL,
IMEI NVARCHAR(200) NULL
)
iNSERT INTO #TEMP VALUES ('0412345678','0412345678','013275009174916')
iNSERT INTO #TEMP VALUES ('013275009174916','0412345678','')
iNSERT INTO #TEMP VALUES ('013568258264650','0412345678','')
SELECT * FROM #TEMP
-- should look ilke
ID SIMNO IMEI
0132750091749160412345678
0135682582646500412345678
0412345678 0412345678 013275009174916
I want to update values in IMEI column if SIMNo matches.
I think it's self join but couldn't come up with any solution...

If SIMNo matches what? What do you want to update the values in IMEI to?

This perhaps?

UPDATE #TEMP

SET IMEI=ID

FROM #TEMP

WHERE ID=SIMNO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

IF SMINO = ID then I want to update IMEI number of that raw to update where SIMNo matched.

ex. Raw 1 & 2 's IMEI number should be updated with value in Raw 3's IMEI number. b'cos their SIMNO is same as ID no. of Raw 3.

Thanks,

Nevyn SSChampion Points: 14210 More actions · Answer 3

Try this:

SELECT t1.ID,t1.SIMNO,COALESCE(t2.IMEI,t1.IMEI) AS IMEI

FROM #temp t1

LEFT OUTER JOIN #temp t2

ON t1.SIMNO = t2.Id

NOTE: There is one problem with the above. If the IMEI in the row with the ID match equals null, it will not take that null. If that is a possibility, and you would want the query to show the null, you can use the following instead:

SELECT t1.ID,t1.SIMNO,CASE WHEN t2.ID IS NULL THEN t1.IMEI ELSE t2.IMEI END AS IMEI

FROM #temp t1

LEFT OUTER JOIN #temp t2

ON t1.SIMNO = t2.Id

Other note: This assumes ID is unique. If its not, let me know

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 4

Hi,

Yes that solves the problem. yes ID is unique identifier and Non Null