MySQL - simple UPDATE statement (need another set of eyes)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I'm looking to set a previously null field TLDid (to the new value of 6)

I'm not understanding why the WHERE in the UPDATE statement is finding zero matches for TLD="com"
when the adjacent SELECT statement finds them all, easily, logically.

Is TLDid a numeric or text field? Your update is SETting a number. If TLDid is text, the number needs to be quoted:

UPDATE `tblDomainNames` SET TLDid = '6' WHERE (TLD = "biz")

Guy Hengel [angelIII / a3]

Hmmm...

We've now got a different potential issue. The queries process the string "biz", your description references "com". It might mean nothing, but you could also be referencing the wrong string.

Kent

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

sorry, both should be biz (i was debugging various dead ends, and copied my example wrong)

i've tried both ' and " (never knew they were different) (just tried yet again and no difference) (note that the SELECT version works with either ' or " )

TLDid is INT 10

Try this one:

SELECT TLD, count(*)
FROM `tblDomainNames`
GROUP BY TLD

And Angel's correct. Use single quote for strings. (I missed that the first time around.) Double quotes have a different meaning.

ASKER

The SELECT statement works reliably in all permutations
It's the cousin UPDATE statement that updates zero rows.
Thus i'm looking for what i'm doing wrong in the UPDATE which has the SAME WHERE phrase.
I've changed over to single quotes and no difference.
thanks...

UPDATE `tblDomainNames` SET TLDid = 6 WHERE (TLD = 'biz')

That should be definitive.

Is anything else going on on that database? Is there an uncommitted transaction pending?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

i'm baffled too - it's a trivial db. everything else works.

here's the results of pasting in (just in case i did something wrong i'm not seeing, i pasted)
PHP MyAdmin plays back:
0 rows affected. ( Query took 0.0030 sec )
UPDATE `tblDomainNames` SET TLDid =6 WHERE (
TLD = 'biz'
)

... and ... (below shows that there exist 14 of them, which is what i would expect)

Your SQL query has been executed successfully
SELECT count( TLD )
FROM `tblDomainNames`
WHERE (
TLD = 'biz'
)

count(TLD)
14

Let's get a better handle on the data. Try this query please....

SELECT TLD, TLDid, count(*)
FROM `tblDomainNames`
WHERE TLD = 'biz'
GROUP BY TLD, TLDid

ASKER

aha you're on to something - hidden chars??! 5 rows is better, but not 14.
Still curious why this would match 5 rows, and SELECT matches 14 (with no wildcards)
and curious why wildcards wouldn't match all 14 (if hidden/special chars is the ONLY root cause here)

(thanks to everyone for you sleuthing patience)

5 rows affected. ( Query took 0.0358 sec )
UPDATE `tblDomainNames` SET TLDid =6 WHERE (
TLD LIKE '%biz%'
)

SOLUTION

wolfgang_93

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Showing rows 0 - 0 ( 1 total, Query took 0.0019 sec)

SELECT TLD, TLDid, count( * )
FROM `tblDomainNames`
WHERE TLD = 'biz'
GROUP BY TLD, TLDid
LIMIT 0 , 30

Browse thru that table in phpmyAdmin and see what is actually in that field. This doesn't make sense at this point.

ASKER

oh wow - indeed not intuitive.

So in summary (and i'll confirm this shortly), there were two issues:

1. my expectations/understanding of the update results: if it's already at the value, then no change and no count for how many rows affected.

2. noise (special/hidden chars) amongst the text to be matched.

ASKER

14 rows affected. ( Query took 0.0054 sec )

UPDATE `tblDomainNames` SET TLDid =7 WHERE (
TLD = 'biz'
)

(YAY) 14 is what i'm looking for,

ASKER

and for the hidden chars, just now without wildcards, zero affected. and adding wildcards:
(note this is a different value, com --> 1 for this different case, which is the case that got me started on all this)

350 rows affected. ( Query took 0.0470 sec )
UPDATE `tblDomainNames` SET TLDid =1 WHERE (
TLD LIKE '%com%'
)

Either of the last two queries that I asked you to run would have shown that. Sorry that we there were so many suggestions coming from different directions and they didn't get examined....

ASKER

you all worked hard on this one. MANY MANY thanks for hanging in there :)

(and sorry for the sequencing, and the confusion)

You're welcome. You might want to look at cleaning up the TLD field so you don't have to re-visit this problem in the future.