MYSQL :: Column Alias On Mass

Differentiate Between Column Alias And Other Column With Same Name
Sample SQL:
SELECT
a,
(SELECT a + 3) AS b,
(SELECT y FROM t2 WHERE b = b)
FROM t1

1. SELECT a + 3 will calculate a column with alias b
2. t2 has a column named b

Question: How can I differentiate between column alias b and t2.b. MySql treats alias b as t2.b in statement (SELECT y FROM t2 WHERE b = b)

Sum Alias Column
I have a database mysql 5.0.51a-community with bookings.
I want to know how many days or weeks has been rented last year compared to this year.
The bookings I insert with arrival and departure date in 2 date type columns.
I do not know how to do it, they way I see it, I must count the days in every booking then sum the days.This seems a good way to me, but gives me error #1054 - Unknown column 'dias' in 'field list'
SELECT arrival, departure TO_DAYS(departure) - TO_DAYS(arrival) as days, typebooking, SUM(days) FROM bookings where typebooking = 'client' and year(arrival) = '2007'

As I understand the error, I can´t sum an alias column,...I could maybe do it with php, but don´t want to do a page for it, and must be a way to do it just with mysql.

Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error.

We can use alias names in WHERE clause.

Is there any solution or other way to achieve same functionality?

QUERY:
select concate(firstname, " ", lastname) as name
from user
where name = "Chetan Parekh";

Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases
but the alias I am using has a space in it, eg 'Device ID'

My querry looks like (simplified, devID is a calculation)

SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123'

and it does not work

but if I use

SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123'

everything works.

How can I use an alias that contains a space in the HAVING clause?

Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this
SELECT money1+money2 AS money WHERE money > 10000
but mysql return an sysntax error Is there any way to use a varible like this in a query?

Can I Check The Value Of An Alias(i Think Its Called An Alias) In A Query.
I was wondering can I check the value of an alias(i think its called an alias) in a query.

At the moment I have this but its not working:

MySQL Code:

SELECT * , ( DAYOFYEAR( ToDate ) - DAYOFYEAR( FromDate ) ) AS NumDays
FROM `tbl_courses`
WHERE NumDays < 3 AND NumDays > 2 AND FromDate >= &#55614;&#57159;-10-17'
ORDER BY FromDate

Mass Installation
I need to install the same database on 40 accounts, all with the same database name, and same table in it... any way of doing this? I've got access to WHM.

Mass SQL DELETE
managed to get put in charge of a forum that uses it extensivly.
Currently, the forum is getting bogged down by the 1.5 million posts so I have archived and wish to delete them.
I have tried a lot of different variations on DELETE, all of which time out. I am left SELECTing the TOP 20000 and then highlighting and deleting them.
I would like to find a quicker, more efficiant way of deleting 1 million rows from a single table (tblThread) that won't timeout.

Mass Insertion
I have a user event manager where they would like to be able to insert recurring events. This is to say that the user specifies that an event would recur (for example) every week at the same time, and then the script would insert all these seperate events. Is there anyway to use a single query to do something that inserts a certain amount of data into the proper records and sets the dates correctly? Or do I need to recursively contruct a query to insert all the records at the code level?

Mass Insert
I have written an application in delphi which performs a mass insert (1000+ lines). However, I am inserting each line into the table one-by-one, e.g.

INSERT INTO invoice_data VALUES (...)

Is there any way I can insert all 1000 in one go? Is this a better way to perform a mass insert?

Help With Mass Update Query
I have a field in my table (1000 records) called company_number. I've just been informed that all records that start with "1" should actually start with "0". So, for a record where compnay_number = 1000400 the number should actually be 01000400. Can anyone suggest how I would approach updating each record via one SQL statement?

SELECT *
FROM `myTable`
WHERE `company_number` LIKE '%100%'
... then what?

Another Way To Mass-upload Than LOAD ??
My ISP gives me phpMyAdmin to get in contact with my MySQL-db
but sadly enough the "IMPORT TEXTFILE TO TABLE" do not work
(gives errormessage "#1045 access denied" and I do not know if it will be fixed,
everything else seems to work allright)
so I'm looking for an other (easy) way to upload 3000 + more records.

Mass Left Join
I would like to join a lot of tables but they all share the same structure. All of them have the same id column. I found a solution, but I'm almost sure there is a more efficient way. My solution is like this:

select id,c1,c2,c3,c4,...,c29 from t1 left join t2 using (id) left join t3 using (id) left join t4 using (id) ...

Mass Updates To A Table
I've got a web-based form that will allow people to update a database
that is displayed as a table. Let's say that there are several columns
across titled Red, Blue, Green... and several rows titled 01, 02, 03...
In each cell, they can fill in some simple text data.

The only way that I can think of to update the database with this
information is using two imbedded loops in Perl:

pseudocode:
for($color=1;$color<=10;++$color) {
for($number=1;$number<=10;++$number) {
update table xyz set value=$value[$color][$number]
where color=$color and number=$number;

This would mean that it would have to execute 100 update statements.
Perhaps this is no big deal and I shouldn't worry about it, but since
I'm relatively new to sql, I'm guessing that there's probably a more
streamlined method to do this.

BTW - I've seen a lot of people asking questions that were vaguely
similar to mine in this group, and they've been told to go to the php
group, even though I didn't see any php-specific code in their
questions. I don't use php and don't have it installed on my server.

Sending Out Mass Email
I have about 10,000 members with emails stored in a database and would like to be able to send emails out to each of them. I'm not sure at all how it would be done though.
I tried a for loop using MySQL queries but none of it worked.
Does anyone have any suggestions on how to do this? I would love to send out a newsletter to my users.

Mass Update Tool
we have so many mass upload tools, load from the file, load from other database built in.
How about mass update tools?
Say I have a table have 3000 products in it.
I want to update the price of these 3000 products with a new table (or excel sheet) of new price by matching the product code (key).Is there a tool for that?

Mass Replace Text In String
I want to replace a few letters in a string throughout my database column and using:

UPDATE products SET pID = REPLACE (pID, "BSC", "BRC")
Did not do the job.

The Strings are variations of 33-BSCE7_14_16 all having BSC that needs to be changed to BRC

Is there a way to use something like "*BSC*" to have it found and replaced?

How To Optimize Mass Data Queries
I am trying to optimize the speed of a query that calculates bias/mean absolute error of forecasts against observations. The forecasts are issued hourly, and make hourly forecasts to the 48th hour. Furthermore, there are about 20 different forecast methods. On top of that, there are 12 different sites. What this adds up to is a really large database. The forecast table has 400K rows, while the observation table as 25K. In order to calculate the above statistics for a given time period, one must pull a lot of data based on the date range. The worst case scenario is that 60,000 rows must be pulled from the forecast table (all stations, methods, and 30 days of fcsts). The problem doesn't seem to be the speed of the query (<.1 sec) as the query is only on the Primary Key (time,station,method), but rather the throughput of the data. I am using PHP to handle the data and present it to the end user. I would estimate that about 15Mb has to go through the pipe before it's processed (ie. converted to an array, and compared to the observations - note, an observation would correspond to say, f010 of a forecast 10 hours earlier, so a parallel join is impossible). The machine I am using is forced to swap in order to execute the script, but it is an old machine (500Mhz processor, 128Mb Ram) that's used for development.

Are there any ways of possibly compressing the inbound data, especially while it is being processed? Right now, I create the big 4-D array before I do the statistics (which are grouped by method), would it help to split up the data feed by method? PHP seems to be most sluggish during the query mysql_query(...), rather than the mysql_fetch_array() looping.

Mass Converting Database Entries
I have an phpbb board and some old MOD changed some entries that it shouldnt. I need to change 1300 entries to default value. How can i do that without having to change one by one manually?

Mass Rename Of Table Prefix
Is it possible to mass rename alot of tables with the prefix bak_ to x_ ?

Mass Replace Characters With MySql
I want to mass replace three htlm character sets in one field in all records in my db. The field name is Subtopic and the db name is ViewTest. I want to replace with in all records. Is there a way to to this?

Weekly Mass Data Relocation
I've got a table located on my website and localhost. The table
maintains a size of about 200MB. The table holds a running 4 weeks of
data (50MB/week). I have a winapp that updates my localhost db each
week and currently, I run a backup on the table using the MySql
Administrator... then I run restore on my website's mysql server.
This deletes the whole table and reinserts each row. This isn't needed
and is a long process.

Is there a way to select a range of rows from the localhost table and
"migrate" those rows to my website's mysql server without following the
process I just described?

Mass Replacing Info In Tables
fairly new to mysql and wondering is there any way to mass replace info in one of my fields. I have a town based member website and they enter their town which goes into the town field of my member table. Problem is the town has officially changed it's name and I am looking for a way to just mass replace everyone's town name in the field. For example all users are from Billington and I want to mass replace every members town field with New Billington the new town name. I understand that I can open up each members profile and type in the new town but I have about 1000 members and was looking for an easier way.

Want To Speed Up Mass INSERTS When There Is Processing Of The Insert_id()...
I am importing data from a large plaintext data set and it is going very slowly. I need to acquire and process the insert_id from each record so I cannot use INSERT DELAYED or LOAD DATA INFILE, for which I could speed things up by increasing the bulk_insert_buffer size. I'm not sure if there is really anything that can be done, but I thought I'd ask.

The looping process is like this.

1. insert row into table 1
2. get insert id
3. one or more inserts on table 2 using the insert_id

Repeat 2000 times or so per page load, because PHP just dies if the file isn't processed in small result sets. It is partly slow because table 1 has several indexes and table 2 has one. The indexes cannot just be added at the end either. In the end there will be +30million rows in table 1 and probably more than 100million in table 2 so if I tried to add the indexes at the end php would die off long before the script could complete.

Export Table Data For Mass Labels
My goal is generate large lists of mailing labels that are based on:

Name
Address
City
State
Zipcode

I created the table and fields, implemented a submission form, and incorporated a link for an admin to download the data into Excel but thats as far as I got.

Is there a better or more thorough approach? I believe that in the end, I want to download this into Word so I can do a mail merge.

Mass Update Of All Database,table,columns For Collation?
Has anyone ever accomplished a mass update off all databases, tables and
columns for collation?

If I try to change the collation/character set for the mysql daemon it
breaks all of our queries because they conflict with the swedish default
that they have been created with. The problem is we have about 90 databases
with hundreds of tables. Is there an easy way to update all of these at once
and that would allow me to start with the correct default at startup.

Alias Name Not Known
The bug I get is "Unknown column 'debt' in 'where clause'" . Why?

SELECT * , ventas.importe_acordado - SUM(cobros.nombre) , ventas.importe_acordado as nombre_venta
FROM ventas
INNER JOIN cobros
ON cobros.venta = ventas.id
WHERE debt > 0
GROUP BY ventas.id

Alias Error
error: Not unique table/alias: 'u'

sql: DELETE u.*,i.*,p.* FROM onig_users u, onig_users_info i, onig_users_profile p WHERE u.users_id=i.users_id AND i.users_id=p.users_id AND u.users_id = 23

i use this same syntax for a different design and had two tables instead of three and it works so I'm confused. Is there something different I should be doing for three tables?

Database Alias
Is it possible to create a database alias? I would like to have something like a symlink to another database. Let's say we have database "gurg" and this one should also be accessible through the name "garg". Is there a way to achieve this?

Dynamic Alias
I'm trying to output the previous 12 months and have the month name as the column heading. It doesnt want to let me use a function as the alias.

CODESELECT Department,
SUM(case when Month=MONTH((CURDATE() - INTERVAL 11 MONTH)) then Total else '0' end) AS DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%M-%Y'),

SELECT Alias
Is it possible to use an alias in the expression for another alias, for example:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + tax AS gross
FROM items

But that dosent work, mysql dosent allow an alias in the SELECT statement (says it cant find a field called tax), which is kind of annoying as it means i have to duplicate all my calculations like this:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + (net / tax_rate) AS gross
FROM items

This might not seem like a big deal in this query, but i have some other far more complicated queries where its just a pain. Is there any way to do this, it seems like it should be possible.

Select Against Alias
Code:
SELECT COUNT(things) AS totalthings WHERE totalthings!=0;

> ERROR 1054 (42S22): Unknown column 'totalthings' in 'where clause'

How can I query against an alias, like above? It's late so maybe I'm missing something obvious..

Alias Problem
I get this error Unknown column 'n.nid' in 'on clause' when I run this query.Code:

SELECT DISTINCT(n.nid), e.event_start FROM event e
INNER JOIN node_access na ON na.nid = n.nid
INNER JOIN node n ON n.nid = e.nid
WHERE (na.grant_view = 1 AND
CONCAT(na.realm, na.gid) IN ('all0','simple_access0'))
AND n.status = 1 AND (
(e.event_start > 1138766400 AND e.event_start < 1141185599)
OR (e.event_end > 1138766400 AND e.event_end < 1141185599)
OR (e.event_start < 1138766400 AND e.event_end > 1141185599))
ORDER BY event_start;
Since node n is declared I can't see what's wrong.

Trouble Ordering By An Alias
Probably something silly here... why am I unable to specify my custom ordering?

SELECT
CASE
WHEN temp.`time` < 11 THEN Ɔ-10 seconds'
WHEN temp.`time` >= 11 AND temp.`time` < 31 THEN ཇ-30 seconds'
WHEN temp.`time` >= 31 AND temp.`time` < 61 THEN ཛ-60 seconds'
WHEN temp.`time` >= 61 AND temp.`time` < 181 THEN Ƈ-3 minutes'
WHEN temp.`time` >= 181 AND temp.`time` < 601 THEN Ɖ-10 minutes'
WHEN temp.`time` >= 601 AND temp.`time` < 1801 THEN ཆ-30 minutes'
ELSE ཚ+ minutes' END AS `visitLength`,
COUNT(*) AS `count`
FROM (SELECT MAX(`time`) - MIN(`time`) AS `time` FROM test_log GROUP BY `visitorId`) temp
GROUP BY `visitLength`
ORDER BY FIELD(`visitLength`, Ɔ-10 seconds', ཇ-30 seconds', ཛ-60 seconds', Ƈ-3 minutes', Ɖ-10 minutes', ཆ-30 minutes', ཚ+ minutes');

ERROR 1054 (42S22): Unknown column 'visitLength' in 'order clause'
Without an ORDER BY, I get the rows I want, without the ordering of course:

+---------------+-------+
| visitLength | count |
+---------------+-------+
| 0-10 seconds | 3823 |
| 1-3 minutes | 796 |
| 10-30 minutes | 394 |
| 11-30 seconds | 430 |
| 3-10 minutes | 682 |
| 30+ minutes | 666 |
| 31-60 seconds | 236 |
+---------------+-------+
7 rows in set (0.23 sec)

Can A Query Alias Be Used As A URL Parameter?
Question: Is is possible to use an alias created in a MySQL query as a URL parameter? (I'm not sure if this is a MySQL issue, a PHP issue, both, or neither). My database table uses self-referencing ids to establish hierarchy. In the temporary example below flow is my PK, and both parent and top are self-foreign. .....

Alias Using Unicode Character
how to use the micro sign in an alias? I want the column name to appear as "Chl (Âµg/L)".

Every Derived Table Must Have Its Own Alias
i have a sub-query i'm trying to execute as follows....and i'm getting the above error message.....

select z.date from (select x.date from bsmtruckrecord as x where x.employee_ida=17) where z.date in (select y.holiday from bsmpublicholiday as y) as z

i have referenced all tables by an alias and i'm still getting the error message saying....
"every derived table must have its own alias"

Not Unique Table/alias
I've written SQL before and I'm sure this is close to correct, but I have never written SQL for MySQL. Where am I going wrong? I'm getting a Not unique table/alias: 'movies' error.

SELECT movies.ID, movies.Title, movies.GenreID, movies.RatingID, movies.UserID, Genres.ID, Genres.genre, Ratings.ID, Ratings.rating, Users.ID, Users.FirstName, Users.LastName, Users.Email, Users.Phone
FROM ((movies INNER JOIN Genre ON movies.GenreID = Genres.ID), movies ON Ratings.ID = movies.RatingID), movies ON movies.UserID = Users.ID

Getting Not Unique Table/alias
Can anyone tell me why im getting this error;

Not unique table/alias: 'q'

When running the following query;

DELETE q.*, r.* FROM questions AS q, replies AS r WHERE r.id_question = q.question_id AND q.question_id = 55

When were alias's first done be mysql as i've just discovered that my provider are running version 4.0.25.

Union Result Going To Other Table Alias
I need to check two tables for a value of 'pending'. Now, this value might exist or might not. So i figured a union would do the trick.

MYSQL
SELECT cats AS alias_one FROM table1 WHERE FIELD = 'pending'UNIONSELECT dogs AS alias_two FROM table2 WHERE FIELD = 'pending'
but if table2 is the only table with the result, mysql returns the contents of col_two but under the alias: alias_one.. how can i get around this?

Not Unique Table/alias: 'tbl_products'
I have the following

SELECT tbl_products.product_id,
tbl_products.product_MerchantProductID,
tbl_prdtscndcats.scndctgry_ID,
tbl_prdtscndcats.scndctgry_Name,
tbl_prdtscndcat_rel.prdt_scnd_rel_product_id,
tbl_prdtscndcat_rel.prdt_scnd_rel_scndcat_id
FROM tbl_products, tbl_prdtscndcats, tbl_prdtscndcat_rel

LEFT OUTER
JOIN tbl_products
ON tbl_products.product_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id
LEFT OUTER
JOIN tbl_prdtscndcats
ON tbl_prdtscndcats.scndctgry_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id

It's saying Not unique table/alias: 'tbl_products'.

Is there anything wrong with the SQL?

Cannot Refer To Alias In Mysql Calculation
Allright, newest problem. I am trying to do most of my calculations in mysql rather than PHP since that makes using the data a lot easier. The problem is I can't seem to use aliases I've assigned to data in further operations.

This query:

SELECT
SUM(CASE WHEN correct = 'y' THEN 1 ELSE 0 END) as top,
SUM(CASE WHEN correct = 'n' THEN 1 WHEN correct = 'y'
THEN 1 ELSE 0 END) as bottom,
ROUND((top/bottom)*100,1) as percent
FROM quiz_answers
LEFT JOIN quiz ON quiz.ID = quiz_answers.quizid
LEFT JOIN cadets ON quiz_answers.cadet_id = cadets.cadet_id
WHERE flight='$row[flight]' AND due>='$start' AND due<='$end'
Does not give me the percent. I get this error.
Quote:

Unknown column 'top' in 'field list'

That is true, there is no column top I wanna use the sum i just assigned as top.

I don't want to put the SUMs in the round function because I may need to use that data too.

Derived Table Alias Error
[MySQL][ODBC 3.51 Driver][mysqld-5.0.24-community-nt]Every derived table must have its own alias

is the ERROR displayed and my QUERY is....

Use An Alias Name For A Field In A Join Ation
I'm using a select like this:

SELECT * FROM `CPESER` INNER JOIN `CONCPE` ON `CPESER`.`id_concpe` = `CONCPE`.`id`
LEFT JOIN `CADSER` ON `CPESER`.`id_cadser` = `CADSER`.`id` LEFT JOIN `CADPRO`
ON `CADSER`.`id_cadpro_data`= `CADPRO`.`id` AND
`CADSER`.`id_cadpro_voice` = `CADPRO`.`id` LEFT JOIN `CONASS` ON
`CONCPE`.`contract` = `CONASS`.`contract` INNER JOIN `CADCLI` ON
`CONASS`.`id_cadcli` = `CADCLI`.`id` ORDER BY `CADCLI`.`name`

but there is a problem: some colums has the same name... it's possilbe to use aliases to the fields?

Alias Mysql ERROR 1064
I am trying to configure the mysql install, following the instructions in the manual, specifically the alias to the mysql path and I get the following error:

mysql> alias mysql=/usr/local/mysql/bin/mysql/
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias mysql=/usr/local/mysql/bin/mysql/' at line 1

Create A Dynamic Alias Name Using A Function
Is this possible?

SELECT

sum(if(EXTRACT(YEAR_MONTH FROM hitdate) = EXTRACT(YEAR_MONTH FROM
now()),1,0)) as EXTRACT(YEAR_MONTH FROM now())

FROM TABLE_NAME

SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it. How do I update a field with an alias?

I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.

Strange Alias And Join Problem On Server But Not Dev Box
I'm running MySQL 5.0.45, php5 and apache 1.3 on windows xp for development. All my tables are MyIsam.
The following query works fine :

MySQL
SELECT
i.image_id, image_filename, region_name, monument_name
FROM
image AS i
LEFT JOIN
image2region USING(image_id)
LEFT JOIN
region USING(region_id)
LEFT JOIN image2monument AS i2m ON (i.image_id=i2m.image_id)
LEFT JOIN monument USING(monument_id)
WHERE region_name='egypt'
LIMIT 0, 8
On the server (MySql v5.0.15) where I plan to host the site I get the following error:

#1054 - Unknown column 'image_filename' in 'field list'

If I give all the tables aliases and prepend all the column names after the SELECT with the aliases it works but I don't want to have to do this with all the queries I've written. I don't understand why it doesn't work on my dev box but not the hosts server.

I have also noticed that this problem only seems to arise on queries with more than two joins.

Has anyone else experienced a problem like this?

Or should I be prepending all columns with aliases as a matter of good practise anyway?

Cheers x

Error 1066 Not Unique Table/alias
I want to select 2 names from Person table. I think a join table would be overboard for this simple requirement, let me know.

person (table)
- id_person
- name

place (table)
- id_place
- name
- id_person1 [fk person.id_person]
- id_person2 [fk person.id_person]

SELECT place.name AS place_name, person.name AS name1, person.name AS name2
FROM place
INNER JOIN person ON place.id_person1=person.id_person
INNER JOIN person ON place.id_person2=person.id_person

ERROR 1066 (42000): Not unique table/alias: 'person'

Wanting output as
place_name: "This Place"
name1: "Fname Lname"
name2: "Fname Lname"