[RESOLVED] Another urgent db query need - sorry but need help

ppowell

UPDATE redesign.student SET student_work_area_other SELECT
REPLACE('anywhere,(.*)', '$1', i.work_area) AS regexp_col FROM
mycompany.interns i, redesign.student s WHERE i.unique_key =
s.unique_key GROUP BY work_area

Of course this does not work, but I have no idea what the MySQL
equivalent will be to something like this.

I basically want to take out PART of a column field value and update it
into another database table. How is that done?

Thanx
Phil

lth2h

I'm not sure how to do that in MySQL (not even sure it is possible).

However you can use PHP. Query the database. Pull out the record you want to update. Use preg_replace to do the regex. Then do an UPDATE with the modified values.

ppowell

mysql> UPDATE student_db.student
-> SET student_db.student.student_internship_area_other =
-> (SELECT SUBSTRING(i.application_area, LOCATE('anywhere', i.application_area) + LENGTH('anywhere') + 2) AS regexp_col
-> FROM olddb.applicant i, student_db.student s
-> WHERE s.unique_key = i.unique_key
-> );
ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause
mysql>

Got me on this one! Using MySQL 4.1.13

Phil

ppowell

This one was just plain undoable, a DBA had to write it for me:

UPDATE student_db.student s
SET s.application_area_other = (
 SELECT SUBSTRING(i.application_area, LOCATE('anywhere', i.application_area) + 
                                                                             LENGTH('anywhere') +
                                                                             2
                                         ) as regexp_col
  FROM olddb.applicant i
  WHERE s.unique_key = i.unique_key
)

WOW
Phil