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

UPDATE `tblDomainNames` SET TLDid =6 WHERE (TLD = "biz")     shows  0 rows affected

but:
SELECT * FROM `tblDomainNames` WHERE (TLD = "biz")     shows 14 rows found.

please help me see what i'm missing
willsherwoodAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Try this:

  SELECT * FROM `tblDomainNames` WHERE (TLD = "biz") and TLDid = 6;

  :)


0
 
willsherwoodAuthor Commented:
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.
0
 
Dave BaldwinFixer of ProblemsCommented:
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")
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Kent OlsenData Warehouse Architect / DBACommented:

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
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please use ' and not " for strings:

UPDATE `tblDomainNames` SET TLDid =6 WHERE (TLD = 'biz')
SELECT * FROM `tblDomainNames` WHERE (TLD = 'biz')  
0
 
willsherwoodAuthor Commented:
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

0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.


0
 
willsherwoodAuthor Commented:
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...
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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?
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
Is 'biz' the only thing in that column or is it part of a URL?  If it is only part then you need:

UPDATE `tblDomainNames` SET TLDid = 6 WHERE TLD LIKE '%biz%')
0
 
willsherwoodAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
willsherwoodAuthor Commented:
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%'
)
0
 
wolfgang_93Connect With a Mentor Commented:
The answer baffled me as this is not the way other database systems work.
But a simple test confirmed what I suspected.

Namely the TLDid values already had a value of 6 and therefore MySQL did not see them
as needing updates. You can confirm that their values are already 6:

select TLDid from `tblDomainNames` where (TLD = 'biz')

If you want to confirm what I mean, issue these commands:

UPDATE `tblDomainNames` SET TLDid = 7 WHERE (TLD = 'biz')
<-- should show 14 rows
UPDATE `tblDomainNames` SET TLDid = 6 WHERE (TLD = 'biz')
<-- should show 14 rows
UPDATE `tblDomainNames` SET TLDid = 6 WHERE (TLD = 'biz')
<-- should show 0 rows
0
 
willsherwoodAuthor Commented:
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
0
 
Dave BaldwinFixer of ProblemsCommented:
Browse thru that table in phpmyAdmin and see what is actually in that field.  This doesn't make sense at this point.
0
 
willsherwoodAuthor Commented:
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.

0
 
willsherwoodAuthor Commented:
14 rows affected. ( Query took 0.0054 sec )

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


(YAY)   14 is what i'm looking for,
0
 
willsherwoodAuthor Commented:
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%'
)
0
 
Kent OlsenData Warehouse Architect / DBACommented:


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....


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

(and sorry for the sequencing, and the confusion)
0
 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
willsherwoodAuthor Commented:
(TLD goes away once i convert to TLDid enumerated entries!)
thanks all...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.