?
Solved

Update with 2 tables and verify integer

Posted on 2011-04-22
9
Medium Priority
?
211 Views
Last Modified: 2012-05-11
Hey folks...
I want to clean up my attached update statement.

Is there a way I can do some kind of a "If Integer" on x.Ref1 instead of the replace statement I have there now?
If @xmlRequestInfo = 'Grid'
	Begin
		UPDATE p 
		SET		p.CTLienStatus = COALESCE(x.CTLienStatus,x.CTLienStatusVer,'NoStatus'),
				p.fileStatus = x.fileStatus,
				p.fileStatusInformation = x.fileStatusInformation
		FROM	PORTAL.dbo.P_UCC p 
		Left Join #XmlPosted x ON p.SeqNumber = cast(Replace(x.Ref1,'Our UCC Filing','0') as int)
	End
select * from #XmlPosted

Open in new window

0
Comment
Question by:lrbrister
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35450363
We need to see the data in x.Ref1. Do you have "Our UCC Filing" in the start/middle/end of the string?
0
 

Author Comment

by:lrbrister
ID: 35450367
Sharath_123:
remembered and found my own solution I think...
Looks like my (test) data below handles it...
declare @table1 table (  
SeqNumber	Int,  
txtInfo		Nvarchar(20)
)  
  
declare @table2 table (  
Ref1 varchar(20),  
txtInfo		Nvarchar(20)  
)  
  
  
insert @table1 values('1', Null)
insert @table1 values('2', Null)
insert @table1 values('3', Null)
insert @table1 values('4', Null)
insert @table1 values('5', Null)
insert @table1 values('6', Null)

insert @table2 values('1', 'Text1')
insert @table2 values('UCC Filing','Text2')
insert @table2 values('3', 'Text3')
insert @table2 values('4', 'Text4')
insert @table2 values('5', 'Text5')
insert @table2 values('6', 'Text6')

Select * from @table1
Select * from @table2

UPDATE p 
SET		p.txtInfo = x.txtInfo
FROM	@table1 p 
Left Join @table2 x ON (p.SeqNumber = case when IsNumeric(x.ref1) = 1 then x.Ref1 else 0 end)

Select * from @table1
Select * from @table2

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 1400 total points
ID: 35450423
Thats sounds good if you do not have other characters like , or . in the Ref1 column of table 2. If so, you need to fix that. Check this example of what I am trying to explain.
declare @table1 table (  
SeqNumber	Int,  
txtInfo		Nvarchar(20)
)  
  
declare @table2 table (  
Ref1 varchar(20),  
txtInfo		Nvarchar(20)  
)  
  
  
insert @table1 values('1', Null)
insert @table1 values('2', Null)
insert @table1 values('3', Null)
insert @table1 values('4', Null)
insert @table1 values('5', Null)
insert @table1 values('6', Null)

insert @table2 values('1', 'Text1')
insert @table2 values('UCC Filing','Text2')
insert @table2 values('3.0', 'Text3')
insert @table2 values('4', 'Text4')
insert @table2 values('5', 'Text5')
insert @table2 values('6', 'Text6')

Select * from @table1
Select * from @table2


UPDATE p 
SET		p.txtInfo = x.txtInfo
FROM	@table1 p 
Left Join @table2 x ON (p.SeqNumber = case when IsNumeric(x.ref1) = 1 then x.Ref1 else 0 end)


Select * from @table1
Select * from @table2

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 600 total points
ID: 35450425
Try this...it is preferred for readability and performance

...
Left Join
(Select * from #XmlPosted where isnumeric(ref1) = 1) x ON p.SeqNumber = x.Ref1


Or better yet, backup in your design figure out how to avoid joining a number to a string.
0
 

Author Comment

by:lrbrister
ID: 35450452
dqmq:
That does look a little better...I'll check it out...and I have no control over the data I'm receiving.  It is entirely possible that anthing from NULL to a string to a white space to an integer can cam in with the data.

I only have control over the data in P_UCC table in my db.
0
 

Author Comment

by:lrbrister
ID: 35450463
Sharath_123:
Looks like you're correct...and dqmq: has a valid point.

You guys want a split?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35450685
OK
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 35451029
You can avoid the issue in Sharath's example if you cast to a number with no decimal places like this:

UPDATE p
SET            p.txtInfo = x.txtInfo
FROM      @table1 p
Left Join @table2 x ON (p.SeqNumber = case when IsNumeric(x.ref1) = 1 then CAST(x.Ref1 AS decimal(15,0)) else 0 end)

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35451243
danrosenthal -  I just gave an example there. There are lot of scenatios around that. You can run with this data and see how your UPDATE statement fails. I think the lrbrister does not have data like this in his original table.

insert @table2 values('3.E4', 'Text3')
insert @table2 values('000,004', 'Text4')
insert @table2 values('$5', 'Text5')
insert @table2 values('06E0', 'Text6')
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

829 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