Hey All,

I just wanted to quickly check to see if this MySQL syntax was correct for what I'm trying to do...

At one point while adding links I had several columns set with a default value. (EX. 0.00, http://) Later I found I shouldn't have been adding these default values. What I would like to do is find and delete all of these default values that still remain in my database while leaving the rest.

Is this the correct syntax to delete the default of 0.00 while leaving added values like 49.99???

DELETE FROM lsql_Links
WHERE price = '0.00'

Looks like it should work but I'm not to familiar with MySql commands.

Thanks a lot!

Hope it's not too late!

As far as I am aware (I'm not a SQL expert) this will delete the links (i.e. the whole record) where the price is '0.00'. I think you want an update - something like this:

UPDATE Links SET price = '' WHERE price = '0.00'

Well, maybe just a little late to save me from that one, but thanks just the same! LOL

I got impatient and when ahead and gave it a go. Just like you said, that query wiped out almost my entire Links Table. Fortunatly I had did a SQL Dump and was quickly able to restore everything back to normal.

That just helped me decided it's about time to buy a book on MySQL.

Thanks for that UPDATE query! I'll go look up the online MySQL manual and give it a try. Whatever I try I can't do any worse than deleting it all like I just did.

Thanks again, I'm sure that update query will do the trick.

Using the query below works fine when removing 'http://' :

UPDATE lsql_Links SET offer_url = '' WHERE offer_url = 'http://'

I just can't seem to get it to UPDATE to remove any of the '0.00' of type FLOAT

The query below returns "0 rows effected" even though almost every link has '0.00' stored right there.

UPDATE lsql_Links SET price = '' WHERE price = '0.00'

Anyone know how to get this to delete any value that's '0.00' ???

Thanks!

How about;

UPDATE lsql_Links SET price = '' WHERE price <= 0

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Why not what afinlr said above =)

I don't know many prices below $0

Thanks for the suggestion Andy

I gave that query a try and had the same results..."Rows Affected: 0"

I thought it may have something to do with the column type being float. Went searching some more and just found this. Take note of the FLOAT description where it mentions problems with calculations and matching rows:

http://www.mysql.com/...en/Column_types.html

which lead me to find this:

http://www.mysql.com/...o_matching_rows.html
"If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values."

Guess I'll keep plugging away at it but if anyone has any other suggestions or comments I'd greatly appreciate it!

Thanks

What about if you try

SELECT * FROM lsql_Links WHERE price = '0.00'

does this return anything?

Could it possibly be that you have set price to not null? It just seems strange that the DELETE command that you tried in the first post worked but the update doesn't.

Hi Afinlr,

Yes, I tired that same select query earlier, and for whatever reason that DOES return the correct columns. Then I can go right from that to the the UPDATE query and nothing is returned at all.

It is also set to NULL.

Problems like these are the ones that fustrate me the most. Using both the DELETE and SELECT query both work but I get nothing when using UPDATE..... "Rows Returned: 0". Bleh

Thanks again for the help

Therefore, it must be that it is finding the rows but doesn't like updating them with the value ''.

I think perhaps you need to use NULL instead of '' - maybe \'NULL' - but I this is where you need someone who knows more about SQL than me.

Sorry to say I had no luck with adding the NULL value either.

I also tried changing it's type to DOUBLE and had no luck with that.

Had the idea fo just editing the SqlDump to find and replace the 0.00's but realized this would be a task as well since Votes also uses a default value of 0.00.

Excuse me while I bang my head against this desk.