Updating Columns
-
Currently I have a column in a MySQL database full of car makes such as Audi, Porsche, and BMW. Although, the make column is filled with either Audi or Audi® or Audi ® and for the other car makes. There are over 2000 rows. I'm trying to figure out how to make every row just "MAKE ®" without typing it because I'm using it to make a menu in php/javascript and it repeats menu items like "Audi" and "Audi®" and "Audi ®." http://i54.tinypic.com/w7zm6c.png I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format. For example I have.. $makes_select = "SELECT DISTINCT make FROM files_avail where shw=1 ORDER BY make"; // Fetching of makes I was looking up the MySQL Reference Pages like on here: http://dev.mysql.com/doc/refman/5.0/en/replace.html And query commands like UPDATE and INSERT. But I am unsure of how to do it in MySQL, I didn't want to do anything before I screw up the entire database :3 Thanks! -Faul
-
Currently I have a column in a MySQL database full of car makes such as Audi, Porsche, and BMW. Although, the make column is filled with either Audi or Audi® or Audi ® and for the other car makes. There are over 2000 rows. I'm trying to figure out how to make every row just "MAKE ®" without typing it because I'm using it to make a menu in php/javascript and it repeats menu items like "Audi" and "Audi®" and "Audi ®." http://i54.tinypic.com/w7zm6c.png I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format. For example I have.. $makes_select = "SELECT DISTINCT make FROM files_avail where shw=1 ORDER BY make"; // Fetching of makes I was looking up the MySQL Reference Pages like on here: http://dev.mysql.com/doc/refman/5.0/en/replace.html And query commands like UPDATE and INSERT. But I am unsure of how to do it in MySQL, I didn't want to do anything before I screw up the entire database :3 Thanks! -Faul
Member 7723899 wrote:
I didn't want to do anything before I screw up the entire database
then make a backup first. or at least copy the relevant table to a new one. if you have PHPmyAdmin, both actions would be very easy.
Member 7723899 wrote:
make every row just "MAKE ®"
a single update could remove every trailing ® in all rows that have one then a single update could remove every trailing space in all rows that have one then a single update could append whatever you want in all rows BTW: I see no use of a suffix " ®" that would be present in every row, it does not add information, it just makes things more difficult IMO. And if you want it to appear on some forms, then that is a presentation issue, something a database should not be involved in. Note: we have a separate MySQL forum! :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Currently I have a column in a MySQL database full of car makes such as Audi, Porsche, and BMW. Although, the make column is filled with either Audi or Audi® or Audi ® and for the other car makes. There are over 2000 rows. I'm trying to figure out how to make every row just "MAKE ®" without typing it because I'm using it to make a menu in php/javascript and it repeats menu items like "Audi" and "Audi®" and "Audi ®." http://i54.tinypic.com/w7zm6c.png I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format. For example I have.. $makes_select = "SELECT DISTINCT make FROM files_avail where shw=1 ORDER BY make"; // Fetching of makes I was looking up the MySQL Reference Pages like on here: http://dev.mysql.com/doc/refman/5.0/en/replace.html And query commands like UPDATE and INSERT. But I am unsure of how to do it in MySQL, I didn't want to do anything before I screw up the entire database :3 Thanks! -Faul
Member 7723899 wrote:
I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format
The steps are 1. Determine what values are in there 2. Determine what values should be in there. 3. Create an algorithmic solution that will convert an incorrect value to a correct one. This is based on steps 1 and 2. 4. Implement 3 in SQL. 5. Run 4. Your description sounds like you have not completed steps 1 and 2 in the above and are attempting to jump directly to step 4. Additionally. 1. Determine if you want to prevent future incorrect values 2. If yes then add a constraint that prevents incorrect values. Based on your description you would do this by creating an enumeration table which is nothing but a table of correct names. Then you use a foreign key from the existing table to the enumeration table.
-
Member 7723899 wrote:
I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format
The steps are 1. Determine what values are in there 2. Determine what values should be in there. 3. Create an algorithmic solution that will convert an incorrect value to a correct one. This is based on steps 1 and 2. 4. Implement 3 in SQL. 5. Run 4. Your description sounds like you have not completed steps 1 and 2 in the above and are attempting to jump directly to step 4. Additionally. 1. Determine if you want to prevent future incorrect values 2. If yes then add a constraint that prevents incorrect values. Based on your description you would do this by creating an enumeration table which is nothing but a table of correct names. Then you use a foreign key from the existing table to the enumeration table.
No, no, no... The steps are 0) Put your left foot in 1) Take your left foot out 2) Put your left foot in 3) Shake it all about 4) Do the hokey pokey
-
Member 7723899 wrote:
I didn't want to do anything before I screw up the entire database
then make a backup first. or at least copy the relevant table to a new one. if you have PHPmyAdmin, both actions would be very easy.
Member 7723899 wrote:
make every row just "MAKE ®"
a single update could remove every trailing ® in all rows that have one then a single update could remove every trailing space in all rows that have one then a single update could append whatever you want in all rows BTW: I see no use of a suffix " ®" that would be present in every row, it does not add information, it just makes things more difficult IMO. And if you want it to appear on some forms, then that is a presentation issue, something a database should not be involved in. Note: we have a separate MySQL forum! :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
I found it out. Simple enough.. I was just only wondering WHERE I should make the SQL code. I'm using SQL Manager Lite for MySQL and you just have to go to the database table, click the DLL tab, edit, delete all the code and write whatever you want and execute.
UPDATE tablename SET columnname = 'whatever' WHERE columname = 'asdf' OR columname = 'asdf ®'
