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

[code]
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
[/code]

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

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

wrote in message
news:1138638553.142319.269100@g49g2000cwa.googlegroups.com.. .
> [code]
> 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
> [/code]
>
> 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.

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

See below, thanx

Bill Karwin wrote:
> wrote in message
> news:1138638553.142319.269100@g49g2000cwa.googlegroups.com.. .
> > [code]
> > 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
> > [/code]
> >
> > 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.

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

wrote in message
news:1138646227.059676.124480@g49g2000cwa.googlegroups.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/en/string-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.

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

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:
> wrote in message
> news:1138646227.059676.124480@g49g2000cwa.googlegroups.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/en/string-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.