?
Solved

SQL Update Join Query

Posted on 2011-05-04
21
Medium Priority
?
280 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:chokka
  • 10
  • 6
  • 4
  • +1
21 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35692812

update GroupTr
set [Generic Code] = T2.[Generic Code]
from Table2 T2
inner join GroupTr on GroupTr.NDC = T2.NDC
0
 

Author Comment

by:chokka
ID: 35692839
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 !!
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35692848

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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:chokka
ID: 35692863
where GroupTr.[Generic Code] IS NULL

is not the correct syntax - Zero's rows affected
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35692866

or

update GroupTr
set GroupTr.[Generic Code] = (select [Generic Code] from Table2 T2 where  GroupTr.NDC = T2.NDC)
where GroupTr.[Generic Code] IS NULL
0
 

Author Comment

by:chokka
ID: 35692881
When a Column Value is Null

We can't just assign is null !!

Syntax will execute .. But 0 rows affected !!

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35692886

does this return any records

set *
 from GroupTr
inner join Table2 T2 on  GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35692888
sorry

select *
 from GroupTr
inner join Table2 T2 on  GroupTr.NDC = T2.NDC
where GroupTr.[Generic Code] IS NULL
0
 

Author Comment

by:chokka
ID: 35692917
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 !!!
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35692931
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 null

Open in new window

0
 

Author Comment

by:chokka
ID: 35692942
Hainkurt - 0 rows affected
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35692944
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 null

Open in new window

0
 

Author Comment

by:chokka
ID: 35692949


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

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35692954
or try this, mybe your data is not null but empty string or just spaces

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])) = ''

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35692976
please post the table structures...

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])) = ''

Open in new window

0
 

Author Comment

by:chokka
ID: 35692986
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
0
 

Author Comment

by:chokka
ID: 35693001
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]

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35693037
Please run the SELECT statement as mentioned in http:#35692888 by ewangoya and check if you have any common records.
0
 

Author Comment

by:chokka
ID: 35693067
ZERO ROWS AFFECTED
select *  from GroupTr
inner join Drug  
on		GroupTr.NDC = Drug.DrugNDCNbr
where	GroupTr.[Generic Code] IS NULL

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35693073
The syntax is not wrong, you dont have valid records to do the update
0
 

Author Comment

by:chokka
ID: 35693107
i am sorry ..! to make the confusion ..!
ewangoya is right
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question