SQL to update records in database

Question

0.00/5 (No votes)

See more:

Hi

I have over 900 records in a database table which one of the fields for example called TM_LOC which contains location path and the item that is on the location.

So normally the location would look like this

D:\CHANNEL_FIVE\BROADCAST\COMPLETED

Then when at items has been put into the location the system that we using normally would store the location against that item for example:

D:\CHANNEL_FIVE\BROADCAST\COMPLETED\EHD0152368

So the EHD0152368 is the name of the file that is on the location.

However, its not coming out as D:\CHANNEL_FIVE\BROADCAST\COMPLETEDEHD0152368 removing the \ between the path location and the item itself.

In PLSQL i want to write an SQL to be able to update all the data that has got this location and that has no \ between the location and the path.

I hope someone could help me out with the sql i m very new to this, so any help would be much appreciated.

Let me know if you need more information
Thanks

Posted 29-Apr-14 23:06pm

Geofferz

Add a Solution

Comments

phil.o 30-Apr-14 5:12am

Could you share the piece of code responsible for the construct of the location?

Geofferz 30-Apr-14 5:22am

Hi, the only code I have is the code creating the table in the database. I think rest is done through the application which has been written by someone else. So the information is entered from a thick client system UI and then it just gets stored into the database table.

I have no access to the code creating the records unfortunately.

So basically I just wanted pointers for example, select * from devices where TM_LOC is...and then an update TM_LOC to ....

if that makes sense?

phil.o 30-Apr-14 5:24am

Ok, I see :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-04-29T23:23:00

Solution 1

Try:

SQL

UPDATE MyTable SET TM_LOC=SUBSTRING(TM_LOC, 0, 36) + '\' + SUBSTRING(TM_LOC, 36, 10000)
WHERE TM_LOC NOT LIKE 'D:\CHANNEL_FIVE\BROADCAST\COMPLETED\%'

Posted 29-Apr-14 23:23pm

OriginalGriff

Comments

Geofferz 30-Apr-14 7:01am

I have the following sql that i want to try and run to return all the items on the following location but doesnt seem to bring back anything. Is the sql correct:

select t.*, t.rowid from tm_mis t where M_CTNR_LOC = '|J:\WIP\ITV\ITV_COMP\COMPLETED\%|'

OriginalGriff 30-Apr-14 8:50am

No: '%' is only a wildcard character in LIKE clauses - it's just another character in EQUALS expressions.

Geofferz 30-Apr-14 12:37pm

Still removing the % does not bring back anything when i run the sql

OriginalGriff 30-Apr-14 13:58pm

:sigh:
No, it won't. Because without a wildcard it amounts to
WHERE 'D:\CHANNEL_FIVE\BROADCAST\COMPLETED\'='D:\CHANNEL_FIVE\BROADCAST\COMPLETED\EHD0152368'
Which is always false.
Use the LIKE clause...with the percent.

Geofferz 2-May-14 7:47am

Awesome thanks OriginalGriff that worked using the like clause

OriginalGriff 2-May-14 8:24am

You're welcome!

Maciej Los 1-May-14 17:01pm

+5!

Geofferz 2-May-14 9:58am

I have another question, If i want to return all items say in all location available that have the path as D:\CHANNEL_FIVE\BROADCAST\COMPLETEDEHD0152368

So here the path should be D:\CHANNEL_FIVE\BROADCAST\COMPLETED\EHD0152368 with a \ between the COMPLETED and the filename EHD0152368. So i want to find anything that has not \ between any filename that might start with M0/EHD/2-

so if there is anything other than a \ at the beginning of these filename starting with M0... it should return. But if there is a \ then then filename it should not.

That make any sense?

Thanks again

OriginalGriff 2-May-14 10:27am

Not a whole lot - but that's probably me.
Do you want to try again with some examples?

Geofferz 2-May-14 11:17am

So for example i have the following loctions:

K:\FILE_DELIVERIES\OTHER\
K:\FINISHED\TRANSCODE_OUTPUT\Profile_2\
R:\ITV\ITV_Compliance\
K:\OUTGOING\LONDON_LIVE\SOURCE\

Normally they should show like this at the end having the file name that is starting with the M0 or EHD etc:

K:\FILE_DELIVERIES\OTHER\M0123456
K:\FINISHED\TRANSCODE_OUTPUT\Profile_2\M0123456
R:\ITV\ITV_Compliance\M0123456
K:\OUTGOING\LONDON_LIVE\SOURCE\M0123456

but like i have explained above the issue is that they coming up as

K:\FILE_DELIVERIES\OTHERM0123456

removing the \ between the \Other (\) and M0

So what i m trying to do is run an sql to be able to find any location and filename without a \ in between the end of path or folder and the file name itself. Because all the devices are different names it could probably be difficult to do or maybe using a loop some sort?

OriginalGriff 2-May-14 11:38am

Ewww! To be honest, I wouldn't do that in SQL - it's something that only really works when you have access the folders themselves, and can check if the files exist:
If file doesn't exist, then look for folder with longest common factor in the path, check the files in that and then correct the DB. Without access to the folder structure - and SQL doesn't have that as far as I know - it's likely to cause errors than it's going to fix!

It's pretty easy in C#...

Geofferz 2-May-14 11:47am

haha nice! Ok thanks I will try find another way maybe searching for records in the system UI should hopefully find all locations, its just long winded that's all.

But thank you so much for all your help really appreciate it! :)

OriginalGriff 2-May-14 11:50am

You're welcome!