Please help - urgent request - I need a query like below ASAP

[phillip.s.powell]

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

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

 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.

UPDATE redesign.student AS s, mycompany.interns AS i
SET s.student_work_area_other = REPLACE(i.work_area, 'anywhere,', '')
WHERE i.unique_key = s.unique_key

You had the order of arguments wrong in the REPLACE function. Also, REPLACE
doesn't understand regular expressions or positional parameters; it only
does fixed strings. But in this case the change you need to make can be
done with fixed strings.

I can't tell what your GROUP BY is intended to do in this query. It seems
irrelevant.

Regards,
Bill K.

[phillip.s.powell]

See below, thanx

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

 > 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.
UPDATE redesign.student AS s, mycompany.interns AS i
SET s.student_work_area_other = REPLACE(i.work_area, 'anywhere,', '')
WHERE i.unique_key = s.unique_key

You had the order of arguments wrong in the REPLACE function. Also, REPLACE
doesn't understand regular expressions or positional parameters; it only
does fixed strings. But in this case the change you need to make can be
done with fixed strings.

I can't see how, because I need the content after the word "anywhere",
and NONE of the conent up to and including "anywhere"; how can a fixed
string REPLACE do that?
I can't tell what your GROUP BY is intended to do in this query. It seems
irrelevant.

Regards,
Bill K.

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I need the content after the word "anywhere",
and NONE of the conent up to and including "anywhere"; how can a fixed
string REPLACE do that?

Aha - I didn't understand the requirements from your nonsense code above.

Well, perhaps you'll have to get more creative.
There are many functions available to you in MySQL.
LOCATE and SUBSTRING are a couple of good ones.
See http://dev.mysql.com/doc/refman/5.0/...functions.html

I'll leave it to you to figure out how to utilize them in this case. I
think it's a bad idea for you to take examples from newsgroup postings and
run them without understanding them.

Good luck,
Bill K.

[phillip.s.powell]

Thanks to you and another DBA (Sybase DBA), this one got solved (I
couldn't have come up with this one to save my life):

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
)

Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I need the content after the word "anywhere",
and NONE of the conent up to and including "anywhere"; how can a fixed
string REPLACE do that?

Aha - I didn't understand the requirements from your nonsense code above.

Well, perhaps you'll have to get more creative.
There are many functions available to you in MySQL.
LOCATE and SUBSTRING are a couple of good ones.
See http://dev.mysql.com/doc/refman/5.0/...functions.html

I'll leave it to you to figure out how to utilize them in this case. I
think it's a bad idea for you to take examples from newsgroup postings and
run them without understanding them.

Good luck,
Bill K.