[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Updating a column using %

Hi,

I have a table called MyTable.
I want to update comments value for the rows that ends with '[address]' and '[phone]' from the paths specified in 'directory' column.
MyTable looks like this.

ID| directory                    |Comments
1 | A\B\C\D\[address]      |Jason
2 | E\F\G\K\[[bank]         |Poland
3 | H\I\J\L\[phone]          |Tree
4 | AA\CA\BB\D\[county]  |Oregon is near!
5 | KI\Y\Z\NC\[salary]      |
.
.
.

I used the query like

Use master
Update MyDB.dbo.MyTable set comments = 'MyFolder'
Where directory like '%address]' or
directory like '%phone]'

This actually works fine.  However, is there any part that I need to modify my script so that it becomes more efficient/elegant code?
0
IzzyTwinkly
Asked:
IzzyTwinkly
4 Solutions
 
sammySeltzerCommented:
I would have done it the same way quite frankly.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

if [address]is coming always @ last place then you can use

Where directory like '%address]' or  directory like '%phone]'

Open in new window



second way is

Where charindex('address',directory) > 0

Open in new window




-bhavesh
0
 
mcs0506Commented:
HI,
I think this query is already written in efficient way as you done

Use master
Update MyDB.dbo.MyTable set comments = 'MyFolder'
Where directory like '%address]' or
directory like '%phone]'


But don't  forget to change your database from Master to your Database name when you executing script.

Regards,

Dani
0
 
LowfatspreadCommented:
using a separate column to store the "immediate" directory name separate from the rest of the path

would allow for a more efficient processing option for the query (assuming you then index the new column) as you
wouldn't need the wildcard and an index lookup could be performed rather than the full table scans required at present...

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now