Simple SQL Update Query Not Easy!

Hi folks, I wonder if someone could help me please.

phpMyAdmin is not somewhere I'm overly familiar with, but I have tried to work out how to run a simple SQL Query. I can't see where the problem is, but there must be one because I keep getting errors.

The Query

I have a property website that has many properties at various prices. I would like to disable (not delete) all live properties under a certain rental price range. Here's what I know:

1. The table name is property
2. The column for price is named pricerent
3. The column for live property is available

So this is what I have written:

Update (table name) set (status) to (No) where the rent is less than (amount). So my query looks like this:

Update property set available='No' where pricerent<200

And the error I get is"

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Update property set available='No' where pricerent<200 LIMIT 0, 30' at line 2

I would really love to know where I'm going wrong here, not least because this is a function that will be very useful as it will save having to disable properties one by one in the CMS admin panel.

Thank you in advance for any help on this.

Aitch

 

I'm assuming you've checked that "available" i set to a varchar or text - ie, it's not a boolean (depending on either being 0 or 1)?
Apart from that, you should be good to go... "UPDATE `property` SET `available` = 'No' WHERE `pricerent`< '200'";
You might wanna try to enclose the 200 in single quotes - depending on what type of column it is.

 

Hi PoPSiCLe. Thanks very much for responding to my post. You lost me a bit on the: a varchar or text - ie, it's not a boolean (depending on either being 0 or 1)? - said it was alien to me I tried to enclose the 200 in single quotes though, but still got errors. I've attached a screen print. Maybe you can see from that what the problem is?


Thanks in advance,

Aitch

 

Could you have a look at the structure of the table, and check the type of each column? The type will be something like VARCHAR, TEXT, INT etc

 

Hi PoPSiCLe, thanks for getting back

OK, I just took a look. There are many types in the table so I looked at the ones that I am trying to write the query for (I hope that's right?). This is what I see:

• pricerent = char(20) utf8_general_ci
• available = varchar(200) utf8_general_ci

Aitch

 

Hm, okay. In that case, the query should work. There is no reason it should fail, really...
Oh, wait. Are you running it like it shows in the picture? Remove the line with "select" that query isn't closed, and those two will fail as they're set now

 

Hi PoPSiCLe,

Thanks once again. You solved a big headache for me today and the query ran through just fine a few moments ago . The down side is that it didn't disable the available properties under a certain price range on the live site, so I will have to try and work out why that is next. But for now, it's good to know where I was going wrong with the query, so at least I can tick that box as done

BTW XXX, I may have to undo that query now so I guess that's another something to learn. However, the site seems to be functioning just fine, so perhaps it's better to just leave well alone if nothing appears to be broken.

Anyway, thanks once again for your input, you've been a great help

Aitch

 

Yeah... You dont have an "undo" function in mysql/phpmyadmin. That is a entirely different approach, and usually needs a programmatical approach. That being said, if this didn't affect the showing on the webpage, that might be because there are other tables involved in asscerning availability. Since I don't know the website's querystructure, nor the database schema, it's impossible to tell what solution might work.

 

Hi PoPSiCLe,

Yes, I already read that there is no undo feature for queries. That said, surely running a reverse query would undo things, but I don't say that with any knowledge or confidence - yet! Even so, I've learned some stuff today that I didn't know yesterday - thanks to you for your help - so I will progress further and perhaps be a little less hasty to run stuff until I know exactly what's what

All the best to you

Aitch

 

Yes, a "reverse query" MIGHT work, but you need to remember that it might backfire. Say you have a table with a boolean column (on/off) - in this table you have several values on (1) and several values off (0) - if you change this column to 1 based on a WHERE column = 0 ALL of them will be 1, which will make a reversal impossible, because you'll have nothing to base the reversal on.
This is why large databases often have several failsafes, like a history-table, a column or table for values being changed etc.
Basically, a reversal mostly only works if your WHERE-condition has more than one comparison, or you have some kind of distinct selector which is not part of the actual values changed (so you can compare on this again)

 

Thanks PoPSiCLe,

Lesson #2. Don't mess around in phpMyAdmin until I know "exactly" what I'm doing I'm going to take time out to learn about this stuff now because would like to manage and maintain my own databases under the hood, so to speak.

Have a great day.

Aitch

 

Just as an FYI I like writing queries in HeidiSQL it's a bit friendlier then phpMyAdmin. It works nicely with MySQL and is free to download.