SQL Update Join Query
SQL 2008 :
I have simple SQL Query
select [Generic Code],
NDC,
[drug Name],
Qty
from GroupTR
In this table, almost 200 rows of Generic Code Column is NULL.
I need to Update those columns from another Table Matching with NDC Column
I feel hard to write an Update Query by Join Statement ..! And it is Inner Join ..
NDC has to match with NDC
I have simple SQL Query
select [Generic Code],
NDC,
[drug Name],
Qty
from GroupTR
In this table, almost 200 rows of Generic Code Column is NULL.
I need to Update those columns from another Table Matching with NDC Column
I feel hard to write an Update Query by Join Statement ..! And it is Inner Join ..
NDC has to match with NDC
ASKER
i am sorry to say ..
This query has to run only for [Generic Code] is Null
I kept
Where [Generic Code] = Null
is not working !!
This query has to run only for [Generic Code] is Null
I kept
Where [Generic Code] = Null
is not working !!
update GroupTr
set GroupTr.[Generic Code] = T2.[Generic Code]
from Table2 T2
inner join GroupTr on GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL
ASKER
where GroupTr.[Generic Code] IS NULL
is not the correct syntax - Zero's rows affected
is not the correct syntax - Zero's rows affected
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When a Column Value is Null
We can't just assign is null !!
Syntax will execute .. But 0 rows affected !!
We can't just assign is null !!
Syntax will execute .. But 0 rows affected !!
does this return any records
set *
from GroupTr
inner join Table2 T2 on GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL
sorry
select *
from GroupTr
inner join Table2 T2 on GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL
select *
from GroupTr
inner join Table2 T2 on GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL
ASKER
o rows affected !!
where GroupTr.[Generic Code] IS NULL
That syntax is wrong ...!
If a column value is Null - We have to write colesce .. something , i forgot the syntax !!!
where GroupTr.[Generic Code] IS NULL
That syntax is wrong ...!
If a column value is Null - We have to write colesce .. something , i forgot the syntax !!!
This should do
update GroupTr
set [Generic Code] = T2.[Generic Code]
from Table2 T2
join GroupTr on GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] is nullASKER
Hainkurt - 0 rows affected
or this inefficient way :)
update GroupTr
set [Generic Code] = (select [Generic Code] from Table2 T2 where GroupTr.NDC = T2.NDC)
where GroupTr.[Generic Code] is nullASKER
Join Table : Drug
Column
NDC = DrugNDCNbr
update GroupTr
set [Generic Code] = T2.[GenericCode]
from drug T2
join GroupTr on GroupTr.NDC = T2.DrugNDCNbr
where GroupTr.[Generic Code] is null
or try this, mybe your data is not null but empty string or just spaces
is GenericCode varchar or char?
is GenericCode varchar or char?
update GroupTr
set [Generic Code] = (select [Generic Code] from Table2 T2 where GroupTr.NDC = T2.NDC)
where [Generic Code] is null or ltrim(rtrim([Generic Code])) = ''
please post the table structures...
and meantime try this one too
and meantime try this one too
update GroupTr
set Ltrim(Rtrim([Generic Code])) = (select Ltrim(Rtrim([Generic Code])) from Table2 T2 where GroupTr.NDC = T2.DrugNDCNbr)
where [Generic Code] is null or ltrim(rtrim([Generic Code])) = ''ASKER
select *
from GroupTr
where [Generic Code] is Null
Above Query returns 361 Rows with Null Values on it.
But on executing this query, i am getting 0 rows affected .. thats crazy !!!
update GroupTr
set GroupTr.[Generic Code] = T2.[GenericCode]
from Drug T2
inner join GroupTr on GroupTr.NDC = T2.DrugNDCNbr
where [Generic Code] is Null
from GroupTr
where [Generic Code] is Null
Above Query returns 361 Rows with Null Values on it.
But on executing this query, i am getting 0 rows affected .. thats crazy !!!
update GroupTr
set GroupTr.[Generic Code] = T2.[GenericCode]
from Drug T2
inner join GroupTr on GroupTr.NDC = T2.DrugNDCNbr
where [Generic Code] is Null
ASKER
Please find the Table Structure : GroupTR
CREATE TABLE [dbo].[GroupTR](
[Generic Code] [float] NULL,
[NDC] [nvarchar](255) NULL,
[Drug Name] [nvarchar](255) NULL,
[Qty] [float] NULL
) ON [PRIMARY]
Please run the SELECT statement as mentioned in http:#35692888 by ewangoya and check if you have any common records.
ASKER
ZERO ROWS AFFECTED
select * from GroupTr
inner join Drug
on GroupTr.NDC = Drug.DrugNDCNbr
where GroupTr.[Generic Code] IS NULL
The syntax is not wrong, you dont have valid records to do the update
ASKER
i am sorry ..! to make the confusion ..!
ewangoya is right
ewangoya is right
update GroupTr
set [Generic Code] = T2.[Generic Code]
from Table2 T2
inner join GroupTr on GroupTr.NDC = T2.NDC