phpMyAdmin problem

[qim]

My table ends with fields Top, Medium, Low. By mistake they have been entered as n,n,y but should be n,y,n

For some reason the code below does not work. Instead of changing the data in Medium and Low from n,y to y,n it leaves the Low as it is and placed ‘0’ in the Medium

What have I done wrong, please?

UPDATE Copy SET Medium = 'y' AND Low = 'n' WHERE Chain = 'HolidayInn' AND Country = 'USA' AND Low ='y'

[spaceshiptrooper]

Why not just delete the column that isn’t right. Then add the column back after the one you want 2nd? If you are looking at the design of where it should be placed, a solution can be exporting the table either as .sql file or exporting as raw ouput so that you can switch the 2 columns. Once you do that, delete the existing table and insert the .sql file or paste the updated version of it to your phpMyAdmin.

Sample

CREATE TABLE IF NOT EXISTS `copy` (
  `Medium` text NOT NULL,
  `Low` text NOT NULL, <-- Want to be 3rd instead of 2nd
  `Chain` text NOT NULL <-- Want to be 2nd instead of 3rd
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Solution

CREATE TABLE IF NOT EXISTS `copy` (
  `Medium` text NOT NULL,
  `Chain` text NOT NULL, <-- Correct now
  `Low` text NOT NULL <-- Correct now
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And what ever value you pass to the query is what ever you get. Where is the full query?

[qim]

Hi spaceshiptrooper

The problem is that I have nearly 10000 records and only a few hundred need changing. If I delete a column it will affect all those that are correct.

To be honest it is a little early in the morning to absorb your post… I never used that kind of code.

from experience my code above should work but does not. What is wrong with it?

[r937]

[quote=“qim, post:3, topic:215826, full:true”]from experience my code above should work but does not. What is wrong with it?[/quote]what’s wrong is the AND

here, try this –

UPDATE Copy SET Medium = 'y' , Low = 'n' WHERE Chain = 'HolidayInn' AND Country = 'USA' AND Low ='y'

[qim]

Magic!

Thanks!