Update values from Table Two with values from Table One

Question

Post reply

Update values from Table Two with values from Table One

cdex3-408940

Old Hand

Points: 349
More actions
August 26, 2009 at 2:14 pm

#135694

I have a table that stores the symbols '>' and '<' in separate fields, as sperate strings in a table (#FT_GreatLessAmts). I need to update a second table (#FT_FinalAmts) with their respective '>' or '<' symbol from first table. However the second table has only one field for either the '>' or '<' symbol. Each specimen will have only one '>' or '<' symbol for each condition. In other words no one condition will have both a '>' and '<'. (E.G. pH will either be '>' or '<' not both.) What is the best way to update the second table?
Specimen is the Unique Identifier for both tables.
IF OBJECT_ID ('#FT_GreatLessAmts')IS NOT NULL
DROP TABLE #FT_GreatLessAmts
IF OBJECT_ID ('#FT_FinalAmts')IS NOT NULL
DROP TABLE #FT_FinalAmts
CREATE TABLE #FT_GreatLessAmts(
Specimen nvarchar(20)NOT NULL,
SGgreat nvarchar(2) NULL,
SGLess nvarchar(2) NULL,
CreatGreat nvarchar(2) NULL,
CreatLess nvarchar(2) NULL,
pHGreat nvarchar(2) NULL,
pHLess nvarchar(2) NULL
)
GO
CREATE TABLE #FT_FinalAmts
(
Specimen nvarchar(20)NOT NULL,
SG_Great_Less nvarchar(2) NULL,
Creat_Great_Less nvarchar(2) NULL,
pH_Great_Less nvarchar(2) NULL
)
GO
INSERT INTO #FT_GreatLessAmts VALUES
(234561, NULL, '<', '>', NULL, NULL, '<' )
INSERT INTO #FT_GreatLessAmts VALUES
(298535, NULL, NULL, NULL, '<', NULL, NULL )
INSERT INTO #FT_GreatLessAmts VALUES
(265401, NULL, '<', NULL, NULL, NULL, '<' )
INSERT INTO #FT_GreatLessAmts VALUES
(222541, NULL, NULL, '>', NULL, '>', NULL )
INSERT INTO #FT_GreatLessAmts VALUES
(278411, '>', NULL, NULL, '<', '>', NULL )
SELECT * FROM #FT_GreatLessAmts
--DROP TABLE #FT_GreatLessAmts
--DROP TABLE #FT_FinalAmts

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

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

if the values in the table are only Null, , then you could use coalesce to return the first non null value it finds.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

insert into #FT_FinalAmts(specimen, SG_Great_Less, Creat_Great_Less, PH_Great_Less)

select specimen, coalesce(SGgreat,SGLess), coalesce(creatGreat,creatLess), coalesce(phGreat,phLess)

from #FT_GreatLessAmts

ISNULL() would work too, in this instance.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

cdex3 Grasshopper Points: 15 More actions · Answer 3

Thanks. COALESCE worked just fine. I like that solution better than using IS NULL in this situation:-)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

I have to agree that coalesce is better overall in that it can handle more than two.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills