Should be easy

[dinno2]

Ok simple question im sure for most of you.
Lets say i have two tables in a DB named final and final1
final has 8 rows named a-h final1 has 2 rows named i-j
I would like to change final where row C = final2 row I and replace that data with row J
Make sence?
any help?

[trooper]

Dinno2

You have to create some form of relationship between the two tables. This is more a question about database design rather than the actual coding.

Take a look at your table designs and think about how the two are related.

[dinno2]

I really dont know anything abou relationships, In tables anyways, though my girlfriend would say different 🙂
A little background might help here.
I have two tables one called final has 9000 entries, 9 rows, with one row, "C" Containing some old data.
The new table final1 has 3000 entries, two rows "I" (Old Data) and "J" (New Data) . Since not all of the codes in the old table "Final" need to be replaced, only 3000 of them, And the new script has the 3000 that need to be replaced, I fuigured there has to be a way of querrying the two tables to Replace the old code with the new code if "C" found the old code it was equal to in "I"
Hope that makes scense. This is bugging me.
Thanks everyone!
Michael

[jeremuck]

is it something like this:

Table one
-------------------------------
name	| food	| book
________________________
joe	| donut	| JS
bob	| grape	| php
phil	| banana	| C++
shiela	| pear	| junk


Table two
----------------------
old	| new
_________________
grape	| banana
pear	| plum

for every old piece of data in table one columen food defined in table two column old, you want to replace it with the new one?

Have you looked at
http://www.mysql.com/doc/en/SELECT.html
?

you could do it two ways.
1.) Create a complex SQL statement that will handle it. I'm sure you can find it on the website I gave you.

2.) Select the records from both queries, and take care of it in PHP.

Is the problem I described above what you have?

[dinno2]

Yes thats exactly what im talking about.
Michael

[jeremuck]

I just read through some of the MySQL docs...
evidently you can't update one table using another.
SO...

1.) Get an Array with all of the OLD values as keys, and NEW values as values.

$query = "SELECT * FROM `two`;";

$VALUES = array();

$result = mysql_query(query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  $VALUES[$row['old']] = $row['value'];
}

Now you've got an array with all the old values and new values.

Now, loop through the array, updating as we go.

//Pieces of our query
$sqlUpdate1 = "UPDATE `old` SET (`food` = '";
$sqlUpdate2 = "') WHERE `food` = '";
$sqlUpdate3 = "'";

foreach($VALUES as $Old=>$New)
{
  $sqlUpdate = $sqlUpdate1 . $New . $sqlUpdate2 . $Old . $sqlUpdate3;
  $mysql_query($sqlUpdate);
}

That should do what you want, although it's very costly because of so many queries.

[dinno2]

Well, thats what i was trying to avoid.
What about creating a new Row, and updating the new data in that new row? Thoughts?

[jeremuck]

Actually, why not buffer the sql statements and perform all of them in one query?

You can do more than one statement can't you?
Of course, even if you can do that, I have no idea what would happen if it failed in the middle somewhere for some reason...

shrug I don't think you can update one table from another.

hmmm
UPDATE one FROM two SET (one.food = two.new) WHERE one.food = two.old;

I don't suppose that works?

or UPDATE one INNER JOIN one, two...

foreach($VALUES as $Old=>$New)
{
  $sqlUpdate = $sqlUpdate1 . $New . $sqlUpdate2 . $Old . $sqlUpdate3;

}
$mysql_query($sqlUpdate);

// instead of


foreach($VALUES as $Old=>$New)
{
  $sqlUpdate = $sqlUpdate1 . $New . $sqlUpdate2 . $Old . $sqlUpdate3;
  $mysql_query($sqlUpdate);
}

I think you are stuck.

I found this comment on the mysql site:

Greg Gray: UPDATE a table from another table : A simple example I've looked for at least 5 times and have had to relearn every time -- Q -- I've got students.id, students.class_id and classes.class_id, classes.count_of_students I want to count the number of times each class_id shows up in the students table and put it in the record for that class in the classes table. (Just to make sure my incrementing process is working right in my front end.) A -- CREATE TABLE s_counts SELECT class_id, count (class_id) as count FROM students GROUP BY class_id.......CREATE TABLE new_classes SELECT c.class_id, s.count FROM classes as c, s_counts as s WHERE c.class_id = s.class_id.....ALTER TABLE classes RENAME classes_old ..... ALTER TABLE new_classes RENAME classes ..... This scoop, switch, drop works to UPDATE a table from another table.... but is this the "professional" or "accepted" way to do it?

[dinno2]

Hmm,
Indeed I guess i am stuck, for now.
I do apreciate everyones time on this. I too have looked at MYSQL manual and every forum looking for answers, some come close, not not quite what im trying to do. I am sure there is a way with MySql for this, Just gotta fuigure it out. That would be silly of them not to include some way of updating one table from another.

Thanks again for the help. <still looking>
Michael