This may not be possible however here is my scenario... actually before I start I would like to say that the root cause of the problem is a very poorly designed database however changing the structure would (of course be beneficial long term) however require an extreme amount of work updating data and query apps (CGI scripts).
I have multiple dB tables which stores names, dates etc.. of when 'tasks' have been carried out. At the centre of all of this is a the 'person' table which unfortunately has a primary key of 'name' (yes thats correct ... you cannot have two John Smiths ! ... this somehow made sense to the designer at the time !!)
e.g. showing only relevent
'person' db
name = John Smith
age = 30
'work_log' db
work_id = 1
description = Task 1
name = John Smith,01/01/2004
work_id = 2
description = Task 2
name = John Smith,awaiting
OK an extreme example however John Smith decides to change his name to John Brown ... is it possible to apply an SQL statement updating only 'John Smith' to 'John Brown' ??
Question:
Does anyone know of any SQL command which could change part of a fields value ... e.g.
John Smith
becomes
John Brown in 'person' db
becomes
John Brown,01/01/2004 in 'work_log' db
also becomes
John Brown,waiting in 'work_log' db
so in effect the query matches only a substring of the field value then replaces this substring with the new substring????
Any help would be well appreciated
This post may also require a re-read to understand ! ;-)
I have multiple dB tables which stores names, dates etc.. of when 'tasks' have been carried out. At the centre of all of this is a the 'person' table which unfortunately has a primary key of 'name' (yes thats correct ... you cannot have two John Smiths ! ... this somehow made sense to the designer at the time !!)
e.g. showing only relevent
'person' db
name = John Smith
age = 30
'work_log' db
work_id = 1
description = Task 1
name = John Smith,01/01/2004
work_id = 2
description = Task 2
name = John Smith,awaiting
OK an extreme example however John Smith decides to change his name to John Brown ... is it possible to apply an SQL statement updating only 'John Smith' to 'John Brown' ??
Question:
Does anyone know of any SQL command which could change part of a fields value ... e.g.
John Smith
becomes
John Brown in 'person' db
becomes
John Brown,01/01/2004 in 'work_log' db
also becomes
John Brown,waiting in 'work_log' db
so in effect the query matches only a substring of the field value then replaces this substring with the new substring????
Any help would be well appreciated
This post may also require a re-read to understand ! ;-)