Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trouble with creating MySQL query

Posted on 2011-04-28
10
Medium Priority
?
307 Views
Last Modified: 2012-05-11
I have a database with 1000s of entries.  I need to change the values of one column called 'stateName'.  I would like to select the rows to change by saying something like "FROM air university to Alabama University, stateName= 2"

Can anyone write the appropriate SQL query to carry out that function?
0
Comment
Question by:NewWebDesigner
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35488330
Try the following. You may get by with removing the LOWER, but I think it may be required for string comparisons.
SELECT *
FROM tblName
WHERE LOWER(statename) BETWEEN 'air university' AND 'alabama university'

Open in new window

0
 
LVL 3

Expert Comment

by:wuyinzhi
ID: 35488459
so you want to change "Air University" (let's say statename = 1) to "Alabama University" (statename = 2)? If so, use update:

UPDATE yourTable set statename = 2 WHERE statename = 1
0
 
LVL 1

Expert Comment

by:hackpin
ID: 35488573
i guess this should be the answer...


update  tblName set statename = 'any'
WHERE fieldname between 'brazil' and 'canada'
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:NewWebDesigner
ID: 35489248
Well,
this didn't work:
$sql = "DELETE 
FROM universities2
WHERE BETWEEN 'Faulkner University' AND 'Stillman College'";

Open in new window


Neither did this:
$sql = "DELETE 
FROM universities2
WHERE universityID BETWEEN LIKE '%Auburn University at Montgomery%' AND LIKE '%Huntsville%'";

Open in new window


any other suggestions?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35489269
Do you want to delete the records
can you told me in which firld 'Faulkner University' these values are store there you need to give the between
if it stored in fieldname like uname then query will be


$sql = "DELETE
FROM universities2
WHERE uname BETWEEN 'Faulkner University' AND 'Stillman College'";
0
 

Author Comment

by:NewWebDesigner
ID: 35489291
I want to change the column called stateID to 2.  I do not want to delete these records.

So I came up with this and it still doesn't work.  Lets see if someone can fix this:
$sql = "UPDATE universities2
SET stateID='2' WHERE unversityID BETWEEN '8' AND '14'";

Open in new window

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35489294
unversityID and statid what is the datatype ? if it in integer then try this


$sql = "UPDATE universities2
SET stateID=2 WHERE unversityID BETWEEN 8 AND 14";
0
 

Author Closing Comment

by:NewWebDesigner
ID: 35489341
THE man, thanks
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35490650
THE man, thanks
Not according to her Eeple  ; )
0
 

Author Comment

by:NewWebDesigner
ID: 35492212
my bad, sorry Pratima, nonetheless thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question