Update MySQL 4.0
Interesting.
I tried this that it MySQL 4.0.
UPDATE tablename set columname1='value1' where columname2='value2';
There was only one value in the database for the where statement, yet it updated 2 rows. Anyone know why? It appears that it is because this table had no primary key.
Here is the table structure:
staff table - primary key staff_id,
title table - primary key title_id,
department table - primary key department_id
crossover table (problem one) - staff_id, title_id, department_id (all foreign keys).
The crossover table was done so because several of the staff are over 2 departments and some have different titles in those departments.
I upgraded my server to MySQL 4.0 and Apache 2.0 over the weekend and so far this is the only noticeable change. I haven't looked in the documentation much yet beyond fixing the grant tables.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
druagord posted this at 16:51 — 17th February 2004.
He has: 335 posts
Joined: May 2003
weird if i have been using Mysql 4.0 for a while now and i didn't notice that behavior. I also use this kind of link table (crossover table). what version are you running. are you sure there is only one row with that value.
IF , ELSE , WHILE isn't that what life is all about
mairving posted this at 17:09 — 17th February 2004.
They have: 2,256 posts
Joined: Feb 2001
The version is 4.018. I tried it several different times and I know that only one record existed for the staff_id. I will play with it again and make totally sure by running a select statement prior to changing the field as a test.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
druagord posted this at 17:13 — 17th February 2004.
He has: 335 posts
Joined: May 2003
Yes a SELECT will assure you there is only one row. But the way i see it if staff_id 1 is in two departement or as 2 title it will be there more then once.
IF , ELSE , WHILE isn't that what life is all about
mairving posted this at 17:21 — 17th February 2004.
They have: 2,256 posts
Joined: Feb 2001
The first time that I did it, I updated based on 'WHERE staff_id='value' . The second time that I updated, I updated it on 'WHERE staff_id='value' and department_id='value2' . Both gave the same results. I was really just adding some values to the database as a test anyway.
Thanks, bud.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.