Funny query problem

[venerable bede]

I have 2 tables, "tblJob" and "tblRegion".

tblJob tblRegion
====== M---1 =======
Job_PK Reg_PK
Reg_FK Reg_Name
Reg_Name

tblJob has a list of jobs and a foreign key pointing to the tblRegion which holds a list of all regions/cities e.t.c.

I have to somehow get the Reg_Name from tblRegion into Reg_Name in tblJob. I know this defeats the objective but I hHAVE to do it.

I have hundreds of records and I have no idea as to the syntax of the update query required.

Can anyone help ?

TIA

[si_the_geek]

what is the DBMS? (Access/SQL-Server/other?)

[venerable bede]

MYSQL

[si_the_geek]

pants.. never used MySQL

ah well, try this anyway:

UPDATE tblJob
SET Reg_Name = tblRegion.Reg_Name
FROM tblRegion
WHERE Reg_FK = tblRegion.Reg_PK

..actually, now I've written it I think it should be fine for all standard DBMS's

[venerable bede]

I get this :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tblregion
WHERE JOB_REGION = tblRegion.REGION_ID' at line

The query
UPDATE tblJob
SET JOB_REGIONTEXT = tblRegion.REGION_NAME
FROM tblregion
WHERE JOB_REGION = tblRegion.REGION_ID

Does MySQL like the '.' operator ?

[si_the_geek]

alternative SQL for you to try:

UPDATE tblJob
SET JOB_REGIONTEXT = (Select REGION_NAME from tblRegion Where REGION_ID = JOB_REGION)

if it doesnt work, I think your best bet would be to check your documentation for the Update syntax.

I've read a few posts on this site that say something like 'MySQL doesnt fully support that yet', or 'you need version X.XX or above'. maybe you'll need to upgrade to be able to do it.

[mendhak]

That's a very funny query. I told it to some of my coworkers, most of them laughed.