mySQL UPDATE Statement

horndog

I have been banging my head against the wall and cannot not get this query right.

Here is the situation:

I have two tables:

exams_access
examdoctors

In the exam_access table there is fields with the following:

Drg
DrS

If the doctor performed the exam it would be marked with a 1.

Now I want to take the above doctors and import them into the examdoctors table and mark Drg as 1 if the exam was performed by them and DrS if the exam was performed by DrS with a 2.

The common fields of BOTH tables are ExamID, which it would have to match between the two

This is the query I thought would work, but craps out when ran against mySQL

UPDATE examdoctors SET DoctorID = 1 WHERE exams_access.ExamID = ExamID AND exams_access.Drg =1;

I get an error stating that exams_access table does not exist, but I am staring right at it

steved

In one part of your question you refer to the exam_access table, in other parts it is exams_access (plural?). Could that be the problem?

horndog

here is the actual query which was copied and pasted:


UPDATE examdoctors SET DoctorID = 1 WHERE exams_access.ExamID = ExamID AND exams_access.Drg =1;

steved

But my point is:

Is the table named exam_access OR exams_access

horndog

steved

Are you able to get PHP to recognize the table at all?

Can you do a select from the table?

horndog

yes, I am able to do a select statement

horndog

I even tried this statement


UPDATE examdoctors SET DoctorID = 1 WHERE exams_access.Drg =1

steved

Can you show your code that you used to do the last update statement?

khaldryck

what version of mysql do you use ?

updates using more than one table is not possible until you have at least mysql 4.0.4

i use a 3.23 version so i never used the update the way you want.

i think you'll have to make it in 2 times ...
first a select query with a join on your 2 tables with the where you use in your current update.

then fetch the result and use it as a where to your update.

like this :

$query="Select examdoctors.id from examdoctors inner join exams_access on exam_access.ExamID=examdoctors.ExamID
where exams_access.DrG=1 ";
$result=mysql_query($query,$db);
while($arr=mysql_fetch_array($result)) {
   $query2="UPDATE examdoctors SET DoctorID = 1 WHERE id='".$arr["id"]."'";
   mysql_query($query2,$db);
}

you'll have to do a little more to do both the actions you wanted but with this hint, you should be able to do it yourself

horndog

I am not using PHP to run the SQL statement. It only needs to be updated once.

I thought I was able to do this once before, but cannot remember how.

I thought maybe it was an Insert Select statement

horndog

here is something... this query only inserted one row, and maybe you guys can help me fine tune it...


INSERT INTO examdoctors (DoctorID) VALUES ("SELECT Drg FROM exams_access WHERE Drg=1 AND exams_access.ExamID = examdoctors.ExamID");

horndog

grrr... I still cannot get it to work

xblue

please check your mysql version. khaldryck was referring to mysql, not php.

INSERT ... SELECT is a supported construct in 3.23, but an UPDATE query that involves more than one table is not yet.

http://www.mysql.com/doc/en/UPDATE.html

horndog

i am running mysql 3.23 I stated this has nothing to do with PHP, but with mysql itself.

khaldryck

if you use mysql 3.23, then you can insert using select with join to retrieve the data, but you can do it for update.

if you do'nt use php to retrieve the where for your update, then yo'ull have to work with mysql variables, but i never use them as i use PHP.

so i can't help more on this.

try to read the chapter of the mysql manual about variables.
i think you can use them to store the result of a query, and then make your update with them.

if you just need to insert (no update) then no prob.

horndog

I finally figured it out:


INSERT INTO examdoctors (ExamID, DoctorID) SELECT ExamID, Drg FROM exams_access WHERE Drg =1;

Then if I need to set another value for the DoctorID of 2 then:


INSERT INTO examdoctors (ExamID,DoctorID)  SELECT ExamID, DrS+1 FROM exams_access WHERE DrS = 1;