Desktop

Ask something
Discussion Locked

Updating Records in SQL database

0 helpful
2 replies

by geeit

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a client who has a SQL derver database. They have reecently moved some data files to a new server. My problem is that within one of the SQL tables is a list of records that are pointing to files on the old server. E.g. w:\msoffice\word\myname\client\file.doc or w:\msoffice\othername\client\file2.doc.
I need to change the first part each of these records and retain the later part e.g. o:\word\myname\client\file.doc. There are 5,000 records in total.

Does anyone know how I can do this within the data
My limited knowledge of SQL doesn't go this far

Thanks

2 replies

by master3bs

You need to use the UPDATE command in SQL. How the command should be structured depends on the Database itself.

The command should be something similar to this:

UPDATE yourtable
SET filenamecolumn = REPLACE(filenamecolumn,'w:\msoffice\word\myname\client\','w:\msoffice\othername\client\')
WHERE filenamecolumn like '%w:\msoffice\word\myname\client\%';

by dsams

With a bit of work and assuming the changes are fairly few and predictable, i.e., strip off one file server or directory name and replace it with another, you can use the update query, and using the length len(), right() and left() functions you can read in the original value into a variable, then strip off the old and replace with the new.

This discussion is closed to new replies.