MySQL Database problem...please help

Hi,

I am running MySql version 5.0.51a in PHPMyAdmin. There is a table with over 900 items in it.

Let's call this table item_cost

One of the fields is called cost_value

What I want to do is find all the records where the cost_value = 0.11 and update all records so the price changes to 0.08

Anyone know how to structure the query statement to do this?

When I browse the table a SQL Query shows this:

SQL query:
SELECT *
FROM `item_cost`
LIMIT 0 , 500

I guess I would need to edit this query to not only find all the records equalling 0.11 but also edit them all to 0.08

Thanks for any help.

Col

 

Something like this line of query should help you Col:

UPDATE `item_cost` SET `cost_value` = '0.08' WHERE `cost_value` = '0.11'

Code (markup):

 

UPDATE item_cost
SET cost_value= 0.08
WHERE cost_value= 0.11

that should be it!

 

Oh you DA MAN!!!! That worked perfectly...thanks buddy!

Co

 

No problem Col

 

UPDATE `item_cost` SET `cost_value` = '0.08' WHERE `cost_value` = '0.11'

 

Thanks for all you guys help...I have a new question:

If I have 2 databases and I want to copy some tables from one db to the other...how can this be done using a sql statement?

 

I think it's much easier if you do it manually Col or you can do it like this:

USE db2;

CREATE TABLE table2 LIKE db1.table1;

INSERT INTO table2  
   SELECT * FROM db1.table1;

Code (markup):

Not sure though

 

This didn't work. All I got was:

SQL query:

USE db2;

MySQL said: Documentation
#1044 - Access denied for user 'db1'@'localhost' to database 'db2'

 

you need to assign the same user to both databases and grant privileges. add user db1@localhost to db2.

 

Done that...still no go.

 

Maybe someone can login and try for me?

 

I think you could probably download a backup type thing of the table and then upload it into the other database. Not sure about using a SQL statement though.

 

OK...This is what I did. Performed a backup of the database. Reinstalled the vBulletin. Then tried to import the database and this is what I come up with:

Why won't this work?

EDIT: OK...it worked partially. I got the stats to show so many threads, posts and users...but not the number of forums and the threads are not displaying either....GRRRRRR

 

Maybe it's not the only table that was needed.

 

Looks like I got it sorted.

Thanks for your help.

Col