Update two tables in database

[Lauj]

Can anyone tell me what's wrong with is line of code?

$query = 'UPDATE emailDir, regUsers SET regUsers.lstName ="'.$txtNmLast.'", regUsers.fstName ="'.$txtNmFirst.'", regUsers.usrEmail ="'.$txtEmailAddrs.'", emailDir.hideStat='.$hdStatus.', emailDir.LstName ="'.$txtNmLast.'", emailDir.MdnName ='.$txtMdnName.', emailDir.FstName ="'.$txtNmFirst.'", emailDir.MdlName='.$txtMdlName.', emailDir.NckName ='.$txtNckName.', emailDir.FstMajor="'.$txtFstMajor.'", emailDir.FstYear="'.$txtFstYear.'", emailDir.FstDegree="'.$txtFstDegree.'", emailDir.SndMajor='.$txtSndMajor.', emailDir.SndYear='.$txtSndYear.', emailDir.SndDegree='.$txtSndDegree.', emailDir.Email="'.$txtEmailAddrs.'", emailDir.Website='.$txtWebsiteAddrs.' WHERE emailDir.dirID='.$txtID;

Here's echo of that query:

UPDATE emailDir, regUsers SET regUsers.lstName ="Lauj", regUsers.fstName ="Chou", regUsers.usrEmail ="[e-mail]", emailDir.hideStat=0, emailDir.LstName ="Lauj", emailDir.MdnName =NULL, emailDir.FstName ="Chou", emailDir.MdlName=NULL, emailDir.NckName =NULL, emailDir.FstMajor="Industrial Technology", emailDir.FstYear="1990", emailDir.FstDegree="BS", emailDir.SndMajor="Risk Control", emailDir.SndYear=2002, emailDir.SndDegree="MS", emailDir.Email="[e-mail]", emailDir.Website=NULL WHERE emailDir.dirID=1234;

I kept getting an error.

Many thanks in advance.

Charlie

[bastien]

And the error is?

[Lauj]

When I copy and paste the query to phpMyAdmin, the error occurred near regUsers SET regUsers.lstName ="Lauj", regUsers.fstName ="Chou", regUsers.usrEmail. Other than this, it just won't work.

Charlie

[bastien]

Can't do it...only one table at a time

[DonChris]

MySQL-Manual says:
"Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]"

I don't know why your code doens't work (perhaps the ';' at the end? - old mysql-version?) but a problem that will occur with your query will be:
only one row in emailDir is going to be updated BUT ALL columns in regUsers -> the 'where' limits only emailDir - don't know if this is what you wanted...

hth
chris

[Lauj]

Many thanks for your help. The MySQL version that we have is MySQL 3.23.43. Now is this mean we can not do multiple table updates? Here's what I have found. I found that this query:
UPDATE emailDir,regUsers SET regUsers.lstName ="Lor", regUsers.fstName ="Chou", regUsers.usrEmail ="[e-mail]" WHERE emailDir.ConstID = regUsers.regID AND regUsers.regID=4703 doesn't work. While this one UPDATE regUsers SET regUsers.lstName ="Lor", regUsers.fstName ="Chou", regUsers.usrEmail ="[e-mail]" WHERE regUsers.regID=4703 works just fine. Even if I'm not updating the emailDir table and only want to compare it on the "WHERE", it still not working. Am I doing something wrong or is there an alternative so I can compare the ID from the emailDir table with the ID on the regUsers table but only update the regUsers table.

Again, thanks!

Charlie

[DonChris]

with version 3.23 you cannot do the multiple-table
updates but multiple-table selects - so you could first select the rows in question...

[Lauj]

Yes, I see your point. I guess I have not choice. Many thanks for your support!!

Charlie