MYSQL :: Using Update To Add Value To Existing Numeric Value

Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not.

Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data.

------------------------------
TABLE: StatisticTypes
id
name
------------------------------
TABLE: Statistics
id
statisticTypeId
value
date
------------------------------

Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...

IF Numeric
I know a little about MySQL but not enough about IF statements infact I didn't even know MySQL could do them.I currently have the following statement Code:

SELECT TRIM(SUBSTRING(name FROM 9 FOR 6)) AS dc,grand_total FROM test02 WHERE name REGEXP '^[a-zA-Z]{4}[0-9]{4}.* Total$'

As you can see I get 5 characters from the name field starting from the 9th character. My problem is some of this are letters and not numbers and I'd like to be able to have an IF statement which says something like
If ouput isn't numeric then leave 00000 instead.

How To The Numeric Value Of The Month?
i'm a table in which i store the month as full month i.e March,October

And i'm going to fetch the data from other table bases on the month value gets from the first table. I'm a date field in the second table which i'm going to check the rows for greater than the fetched month..

How i can handle this.

If i can get the values as 10,4 for October,March respectively then i can easily get the data from the other table.

Numeric Datatypes
I've been using MySQL for some time now but I think i am misusing an aspect of it.

During the database design phase when defining datatypes what are the implications for using values of 'M' in the following INT(M)

I have been assuming that this is setting the max length of the value stored within.

so &#55622;&#56959;' would be allowed for INT(4) but not for INT(3)

I suspect this is wrong and would be grateful if somebody could clarify this for me.

SQL Order By Is Numeric
I need to order by some selection in mysql, but I dont now if the content of a field is numeric or alpha.

How can I do that?

does an SQL-equivalent function from php "isnumeric" exists?

Numeric Check
I am looking for some function that tests if a value is numeric. I could not
find a function for this.

At the moment I am using "WHERE CONV(myfield,10,10)!=0" but I suppose there
must be a better way.

Numeric Format
How can i make a select from a double field in this format: 1.235,45

I am trying by this way:

select format(mynumer,2) from tblnumbers
to add to by unbound in a DataGridView (VB.NET). and always receive the same result:
1,235.45

How can do to receive 1.235,45? "EUROPEAN Numeric format"

I mead by me Regional Options Settings.

Sorting Numeric Strings
I have a column of type varchar that stores many different numbers. Say for example there are 3 rows: 17.95, 199.95 and 139.95 When I SORT BY this column, I get 139.95, 17.95, 199.95, (in that order) because it's comparing them as strings. Is there a way to make the sql compare them as numbers?

Restoring Numeric Databases
I'm developing a database application that uses numeric databases. At
this point not using numeric databases is not an option. Doing a
mysqldump works. However when trying to restore the database with
"mysql -u root -psecret < backup.sql" gives the following error
message:

ERROR 1064 at line 11: You have an error in your SQL syntax near
'123456789' at line 1

Line 1 has the following line:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 123456789;

I know this will generate an error. The correct syntax would be:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `123456789`;

How can I use mysqldump and restore to work properly with numeric
databases?

ORDER BY Alphanumeric, Numeric
How can i get the following order?

ab
a1
bc
bx
b4
zaq
z5
1ax
9b

Field type is varchar(50) latin1_general_ci

Replace Alphanumeric To Numeric
I have some alphanumeric values in a column and now i have to replace all those alphanumeric values to numeric one.. How it is possible by a single query.

Is it possible by using regex or replace function().. Let me know in detailed.

Remove Non-Numeric Characters
I have a few fields in a table that store phone numbers. The data in this field is taken from a web form where it is entered free-form. Most of the records in the table are in the following formats:

727-555-1212
(727) 555-1212
.. etc

I am creating a phone number search for this app and need to be able to pull back all records where the phone number searched for matches a phone number in the table. The problem is, however, that the phone number in the database may have extra characters such as spaces, parentheses, hyphens, etc. Is there a way I can grab just the numeric values from one of these fields and query based on that? Basically, I need a function that will take "(727) 555-1212" and turn it into "7275551212". What's the best way for me to do this? I know I could hard code a few nested REPLACE commands on the field to remove spaces, parentheses, and hyphens, but it's possible there is other non-numeric data in these fields as well.

Increase Numeric Field
im wondering i need to inset into a field a increse number but only if another field is NOT LIKE OF something.
so i was thinking is there an way to create a table like:

id int auto inc primary
user
type_of_user
code_id

the code_id will be incremented only if type_of_user is not like no

and it will read the last position gave and increse it there a way to make it from the table or do i need to make it by hand.

Numeric Or Char Datas
I have to log the IP...
what is better/faster to read/write?

a) ip2long (php function) that converts the number to a INT
b) a 16 varchar field to log for example "111.222.111.222" ?

in general, is better numeric or character fields ?

Quotes Around Numeric Data
I've been jumping from databases to databases, and I was wondering for MySQL, which of these is recommended:
WHERE col = '4'
OR
WHERE col = 4
Do quotes around the numeric integer matter?

ORDER BY And Numeric Values
I'm trying to pull rows out of my table and then order them with a simple ORDER BY, the only problem is, I'm trying to order by numeric values that have been saved as varchar.
This is really screwing up the ordering, for example, 949,746.00 is coming up as being greater than 24,000,000.00. I can't merely change the column type to numerical because the table is being generated dynamically--I don't know which columns are going to contain numbers and which are going to have text.
I'm using PHP to generate the MySQL statements to make the tables, so I figure I could write a check to see which columns have values of numbers, then change its type. This would take considerable time though, so I was hoping that there is an easier and quick solution that I'm probably just overlooking.

Numeric Data Types
why would I use a float or double over a Numeric or Decimal data type if it is not 100% accurate?

Calculation Percentage On Non-numeric Columns
Trying to calculate the percentages of artist's from each country in our database.

I am drawing from 2 tables, the artists table which has a short country code and the citylatlong table which has the full country name.

Since the artists.country column is a varchar(2), is it possible to get the number of artists per country as a percentage.

Here's the query I'm trying:

SELECT artists.id, artists.country, countries.countryLONG, (SUM( artists.country ) / COUNT(artists.country )) *100 percentage FROM artists inner join (select countrySHORT, countryLONG from citylatlong GROUP BY countrySHORT, countryLONG) countries ON artists.country = countries.countrySHORT GROUP BY country ORDER BY country ASC limit 10 ;

The percentage column always comes out as 0.

When Country Value Is Numeric, Order By City
data in myTable

(id) country city
(1) America Washington
(3) Japan Tokyo
(4) 1 Chicago
(5) France paris
(6) 3 Nagoya
I have data in myTable like the above.

I like to produce order by country when country value is not numeric and order by city when country value is numeric.

The following would-be code doesn't work correctly, but it will show what I want.

would-be code

select t1.id,t1.country, t1.city
from myTable t1
left join myTable t2 on t2.country=t1.id

order by case when t1.top rlike '^[0-9]+$'
then t1.city else t1.country end

target result

(1) America Washington
(4) 1 Chicago
(5) France paris
(3) Japan Tokyo
(6) 3 Nagoya

//alphabetical order : A -> C -> F -> J -> N

2nd Request, Alpha & Numeric Queries
I am trying to pass through a string that I am exploding into individual values to use in a query. For example, I want to pull the following three values through a query:

99201
A0040
99211

When I run it through the following query (it's missing some html tages, just trying to do a dry run) i twill run the first value through (regardless if it's a alpha based or numeric based), but after that it hits the else phase of the if (mysql_num_rows($mcalookup) >= '1') { code.

Any clues what's going on? BTW< if I strip off the ' ' around the variable, it dies when it hits the alpha based variable. Code:

Retreive Only Numeric Data Types
how to return colums that are numeric from a table?

Database Cleanup - Strip Non Numeric
how to strip non numeric characters from a field?

eg 999-M9087
should be 9999087

Sort By Numeric Part Of A Field
I must sort a table width only one column. Inside the field I have, for
example, hard disk capacity (something like '100Gb' or '80Gb' or
'80Gbyte' or 'Gb80').

So, if I use ORDER BY ... DESC, 80Gb will be before 100Gb and other
errors.

How can i sort the table only on the numeric part of the field?

I'm trying using CAST() but with no results.

Non-Numeric Result From SELECT COUNT(*)
I'm using MySQL 4.1.11 and MyODBC 3.51.11.
I've a problem with SELECT COUNT(*) query.

I'm using mysql in ASP. I'm executing this query for example:

Select Count(*) as TotalMembers From Members; //with recordset("Totalmembers")
-- or --
Select Count(*) From Members; // with recordset(0)

but both of these queries aren't giving Numeric (Integer) results...
I'm cotrolling them with IsNumeric function by ASP. But it gives False result.

Also i cannot using mathematical operators such like +-*/ . Because result isn't numeric. And an error occurs.

If you know asp, could you try to use math operators with select count(*) result?

Pulling Numeric Data Type
I just recently started using mysql and have a couple phone numbers stored in the database. I insert the number into the DB in pretty much the same fashion as I used to do with access. When I pull it back out however, it doesn't keep the format:

If I put in:
9165551212

It returns:
9.16662E+09

How can I get it to return the number as I entered it, aside from changing the data type to var char. Or how can I convert the returned number to the desired format.

Import CSV File With Numeric Data
I am trying to import a CSV file which was exported from another mysql database with comma separation and quote delimeters. A numeric column is enclosed in quotes and cannot be directly imported into the target table since it is now a string.

How should I import this data? I have tried to import it to an intermediate table with the id column defined as a varchar but I have been unable to convert that value back to a number.

Numeric Sorting Of A String Field
I have an issue with sorting sail numbers for sail boats. Sail numbers may optionally contain a country prefix (can5003, or US345), hence the string. So.. I need to sort the sail numbers numerically by the integer component only (ignorning the chars).

Sample data
1352
US 2005
74272
CAN801

Needs to look like this.
CAN801 - sorted as 801
1352 - sorted as 1352
US 2005 - sorted as 2005
74272 - sorted as 74272

I don't quite know where to start.. a generated field that is 0 lpadded to some max length? - not sure how to drop the chars.

Adding Numeric Mysql Field Values
I am having a mysql field that holds a numeric value in each of their rows, like 2, 5, 0, 9, etc. I need to add them and get their result (2+5+0+9+ ...)

Wondering if there's a short cut way to do that using a mysql command ... or, i'll have to fetch the values in an array and add them using php.

Decimal / Numeric Value Exceeds Maximum Precision
I have a table with a Decimal(3,1) and a SMALLINT.
When I insert 120,0 (for the decimal) the value is stored as 99.0
I'm used that I get a error like Decimal value exceeds maximum precision.
This is also when I insert a value greater then 32767 for the smallint.

I want to get an error. But how do I get this.

Format Function In SQL String To Return From Numeric Field
How would i form a format function in SQL string to return from numeric
field in table to string:

1 --> '001'
5 --> '005'
57 --> '057'
125 --> '125'

My.cnf Not Existing
this is my first posting here so plz do not flame me too much if my quest=
ion=20
marks me as a total noob.

My problem is, i play around a bit with mysql (4.0.13) and want to test t=
he=20
Replication. The Documentation at http://www.mysql.com says i have to do =
the=20
following:
Stop databases - done.
Build tarball of data and bring it on slave - done.
Modify my.cnf - ???

Here is my problem. There is no my.cnf existing on my machine. Maybe its=20
called different with the newer version? Or can it be considered empty an=
d i=20
have to build a new one? But if i have to, in which directory would i do=20
that?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

How To Add A New Row To An Existing Table?
I am able to create database, tables, inserting values, and so forth. My question is this:

Let's say I have a table on the list of items I owned, and some time later, I need to add a new item between after row 12 and before 13. How do I do that? I did look at MySQL 5.0 Manual and didn't find anything similar to what I wanted to do.

Check For Existing Row ?
How do I determine if a row already exists ?

// see if a row has id=100

$id=100;
$query="SELECT * FROM mytable WHERE id=$id";
$result=mysql_query($query);

...what do I check now ?

Using Existing Database
I am setting up a new dev. machine and need to get an old database into the new installation of MySql. There are two databases showing on the database list 'mysql' and 'test'.
Can I drop the old database (contained in a folder) straight in with the existing databases and get any sense from it?

Existing Database
I'm using MySQL 4.1.9-nt & VC++ 6.0 .
how to get all the database names and table names?

I'm using the VC++ syntax,

CRecordset rs(&db); //here db is object of CDatabase class.
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"show databases ");

Information From Existing Db
Dont know much about mySQL, so excuse my potential ignorance here :
Is it possible for a table to write information to another table automatically?
For example, if on "table1" I have a column called "code", can I write the entire contents of a specified row to another table called "abc" if the code "abc" appears in the "code" column of "table1"

Add A Record To An Existing Recordset?
I have a MySQL table with about 30 records, I use a form to insert data into the first 24. (like; Name, Phone, etc...)

The last 6 (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) are the names of uploaded images (which are uploaded from a seperate form.

How do I (Or what MySQL statement do I use) to insert the 6 names into the recordset?
(Each image name will be saved into a php variable. ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)

How Do I Add Default To Existing Column?
It should be easy but i've searched from mysql manual to here...

Moving Existing Fields Out
Can someone tell me what would be the best way to move fields out of an existing table into new tables?

Best Way To Get Data From Existing Database
what I'm trying to do is get data from an exhisting oracle database that is not looked after by myself but I have access to. What I want is for my MySql database to mirror the oracle one or update every 5mins or so. I have done a quick search but as I have only made simple databases before I'm not sure what to search for!

Attaching An Existing Database
I would like to attach an existing database (I have the files from another system) to MySQL running on a recovery machine - yeah, the Linux system got screwed, but I managed to recover the files.

So, having the database files (.myi, .myd and .frm), how do I go about incorporating them into the current system? (Like "attach database" in MSSQL)

Foreign Key To Existing Table?
Is it possible to add a foreign key to a table that already exists? Or do I have to drop the table and recreate it?

Insert Row Into Existing Table
I have a table with rougly 70 threads, I now need to go back and insert a couple new entries into my table, but they cant go at the end, they need to go in the middle, say like ID 40,41,and 42. How do I do this?

Insert A Duplicate Of An Existing Row
I have a table with an auto-increment columns (absid) and I want to take a row with a certain absid and re-insert it as a new row with a new absid (but all other columns as from the copied row). Running mysql 3.23.55. Might something like the following work?

insert into mytable (absid,*) values 0 select * from mytable where absid=20;

or something similar? With 3.23.55 I suspect it won't work anyway but I have no access to a version 4 installation and wouldn't want to upgrade if what I want to do is inherently not possible.

Accessing Existing DB From Files Off A CD
I inherited a project from another developer who was fired. There is no database documentation. There is both an SQL backup file and the original database files on a CD. I have recreated the db from the backup file and that worked perfectly. However, I don't have full confidence in the accuracy of the backup file. I was wondering if I can somehow access the db from the files on the CD.

I copied the files to a folder on my machine and then changed the MySQL data folder setting to that folder. Unfortunately MySQL wouldn't start. What would I need to do to be able to have MySQL use the files copied from the CD as the data files?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..

Using Update To Add Value To Existing Numeric Value

I have users inputting hours into a mysql database. Is there a way to have a mysql statement take my input and add it to the existing value in a record? I suppose I could just select that value and use php to add the 2 values but was thinking mysql might have a function to do this for me.