Can I: Export Column, Optimize & Fill New Column With Value To Original Column?
I have a database with over 40,000 rows and 28 columns (learned how to import large files by changing php.ini!).
Using Excel, I am able to: (A) copy and paste original column (e.g., SIZE_TEXT) (B) ALONG with each records' UNIQUE_KEY, (C) sort SIZE_TEXT column, (D) filter for unique values, (E) fill new column with its SIZE_TEXT__KEY, and then (F) import the new SIZE_TEXT_KEY value into the database by creating a new column or pasting over the columns original value.
However, I think this can be performed, with less potential for error, using phpMyAdmin, but I haven't found anything demonstrating how to perform these actions.
I searched the terms optimization and normalization.
I am hoping one can use phpMyAdmin to:
1) create a new table with export column of the original table (SIZE_TEXT_TABLE),
2) sort new table for duplicates and show only unique values, then
3) import the column results into each Unique Records original column (SIZE_TEXT)
4) link new column values to Foreign Key (the Parent Table's SIZE_TEXT_KEY)
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting From Column A Where Column B Matches Column A Twice?
Here's a table called Creatures containing Creatures and EntryIDs: Code: [Creatures] Creature EntryID ================== fish 100 cat 100 fish 200 bird 200 pig 300 bird 400 I would like to select all EntryIDs that contain both "fish" and "bird". From the above table, "200" should be returned, seeing as both fish and bird are the only rows that both use the same EntryID. How can I do this? Do I need to use Group By or Left Join or something?
View Replies !
Export/import Of Column Names With Space
I am using MySQL 4.0. Say that I have a table named "mytable" having a column "ID" and a column "test columnname". When dumping: mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt it gives someting like: CREATE TABLE mytable(ID int(11) NOT NULL, test columnname text NOT NULL); Then I use the mysql command to import myfile.txt in another database: mysql DATABASE -uUSER -hHOST -pPASS < myfile.txt but it fails with: "ERROR 1064 at line 1: 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 'test columnname text NOT NULL)' at line 1" Obviously, the mysql command can't handle column names with spaces. Is this a bug? I would say so - mysqldump should put the column names between quotes or so (alternatively, mysql shouldn't allow spaces in column names). My problem is that I have to work with these columns as they are. They're also referred to from a lot of queries in the API's. Any possible workarounds?
View Replies !
ODBC Export Error: Incorrect Column Name
I have a project that I've developed in MS Access but now I'm trying to port it over to a MySQL server. In my access version I am exporting from a piece of software to the access database over ODBC. I've installed the MySQL ODBC driver, setup a datasource in the software and connected to a blank database setup on the server. However, when I try to export I get the following error: [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.67-community-nt]Incorrect column name 'Floor Volume ' (1166) [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.67-community-nt]Incorrect column name 'Floor Volume ' (1166) Some of the tables do get generated so it works somewhat, but it's obvious that there are a number of tables missing. Also when I do get this working how can I ensure that when I export through ODBC that the original tables in the database are cleared and completely replaced with the new data?
View Replies !
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
View Replies !
A Date Column That Auto-updates When Any Column In The Row Is Updated
Let's say I have a table with several columns. I would like to add a column called "date last updated". How this would work is that any time one of the other columns in that row was updated, the 'date last updated' column would update. I *know* how to do this with PHP, but here is the special part... Is there a way to have this automatically just within MySQL, so that EVEN IF if update a column manually through the command line or PHPmyAdmin, the 'date last updated' field updates.
View Replies !
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
View Replies !
Order By On Column And Reoder By Another Column
im using php and mysql, but i would like from the sql statement to get the results of a query order by a column and then reorder by another column that is: i want to select the last four entries accoding to the date. SELECT * FROM table ORDER BY Date DESC LIMIT 4 but I also want to order those 4 entries i got, according to their values at the order column like this SELECT * FROM table ORDER BY ordercol ASC LIMIT 4 how can i put this two together? i thougt of SELECT * FROM table ORDER BY Date DESC, ordercol ASC LIMIT 4 but it gets the last four entries and then if two entries have the same date it orders them according to the ordercol, how can it do it?
View Replies !
Re-name A Column
I am using Query Browser version 1.1.2, MySQL Administrator 1.0.14, MySQL Version 4.1.7-nt on Windows XP Service Pack 2 ALTER TABLE `purpleflavours`.`purchaseorderitem` CHANGE COLUMN `PurchaseCost` `PurchaseOrderItemCost` DOUBLE UNSIGNED ZEROFILL CHARACTER SET NULL COLLATE NULL NOT NULL DEFAULT 0000000000000000000000 Generates when I click the apply changes button in the MySQL Table editor window after changing the column name. This generates an error telling me I have an error in my SQL Syntax Can anyone tell me what is wrong? By the way I can use the table editor to change VarChar column names with no problem but I always get a syntax error if the column type is not a VarChar, i.e. Double, DateTime, MediumBlob, Integer etc.
View Replies !
As Column
I have a query that selects... YEAR(date) as theyear But I get an error every time I try to use 'theyear' in my WHERE clause... It just says 'Unkown column "theyear"'
View Replies !
Column Value
I made this query which has this sum fuction which calculates the value of the contents of the individual rows. e.g SELECT (SUM(case when R1='yes' then 1 else 0 end) + SUM(case when R2='yes' then 1 else 0 end) + SUM(case when R3='yes' then 1 else 0 end) ) AS yes, (SUM(case when R1='no' then 1 else 0 end) + SUM(case when R2='no' then 1 else 0 end) + SUM(case when R3='no' then 1 else 0 end) ) AS no FROM tblname1 where faculty_name = 'abc' AND student_name = 'efg; i want to display the values of the faculty_name and the student _name and one other column (comments) . but it shows error. i tried doing union but its not working either
View Replies !
Where Column
I initially thought when doing a query with a where clause that has WHERE something='value' that it would look for exact value. but when I ran a query like this: "SELECT * FROM table where id='65'or id='64' it brings back the row where id=65 (id is column type: id INT(5) NOT NULL auto_increment) (I was testing some queries to verify user input and its results in php)
View Replies !
SUM(column)
I trying to add a column where the field is equal to 0' I try something like this but does not work SUM(IF(pitcherstats.win = 0)) AS loose,
View Replies !
Associated Column
Consider the following dataset: ID / Category 1 / 1 2 / 1 2 / 2 3 / 2 If I SELECT where Category = 1 or Category = 2, I will get two results. What would be the correct way to accomplish obtaining only the IDs which contain BOTH Category 1 and 2 (In this example, ID 2)?? -- Basically a boolean 'AND' search of some sort... saying 'SELECT * .. WHERE Category = 1 AND Category = 2' obviously doesn't work.
View Replies !
Set Column Value
i have 2 colmuns that i want to use the difference between them as the value of another column. all columns are in the same table. the type of the 2 column is "Date". so what i want is to get the difference between the 2 dates and assign it to the 3rd column.
View Replies !
Row Into Column
i have this structure of tables: tehnic_detalis(id_detail, detail_name) products(id_product, id_detail, detail_value) I wish is to transpose row from tehnic_detalis into column for products in something like this short_description(id_product, detail_name1, detail_name2...)
View Replies !
Column Name Containing . (dot)
I have a query that contains some column names with a dot (.) as part of the column name. How can I select these columns in another query? Example: The first column is 'Name', the second 'Name.Old' Now: SELECT * FROM (qryName) works fine. SELECT Name FROM (qryName) works fine. SELECT Name, Name.Old FROM (qryName) does NOT work.
View Replies !
Column = Column + 0
Is column = column + 0 allowed as part of a MySQL query? I am experiencing problems using it, but I can't be sure if it is that or something else in the code..
View Replies !
Add New Column
I Want To Add New Colume In Exitsing Table Which Have 3000 Records New Columns Not Contain Null Values How To Add Then Not Null Column In Exitsing Table
View Replies !
Cannot Change Column Name
I inadvertantly named a field "in" mysql> select * from guestbook; +----+-------+-------+-----------------+----------------+ | id | fname | lname | comments | in | +----+-------+-------+-----------------+----------------+ | 1 | Mick | White | Test 123 | 20050208000000 | | 2 | Ann | White | Hello World | 20050213101948 | | 3 | | | It's a nice day | 20050213102405 | +----+-------+-------+-----------------+----------------+ mysql> select in from guestbook; ERROR 1064: 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 'in from guestbook' at line 1 But I can't seem to be able to change it: mysql> alter guestbook change in time_in; ERROR 1064: 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 'guestbook change in time_in' at line 1 The table: mysql> desc guestbook; +----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+----------------+ | id | int(4) unsigned | | PRI | NULL | auto_increment | | fname | varchar(10) | | PRI | | | | lname | varchar(10) | | | | | | comments | text | | | | | | in | timestamp(14) | YES | | NULL | | +----------+-----------------+------+-----+---------+----------------+
View Replies !
Auto Column
I want to select a single row called "row1" but let mysql auto append a NON-exist column call "row2" of it. And i want that non-exist column be auto-increment. how to? If i "select row1 from mytable", then i get ----------- - row1 - ----------- - a - - b - - c - - d - ----------- but i want: ---------------------- - row1 -- row1 - ---------------------- - a - 1 - - b - 2 - - c - 3 - - d - 4 - ----------------------
View Replies !
Best Practice Column
Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal columns. But it appears that such values are stored as ASCII strings, which would be inefficient for calculations (requiring conversion to a numeric type for each calculation). I guess the alternative would be to use integer columns (and multiply by 100 to store the value as total cents). My particular context is a PHP/MySQL sales system.sie.nctu.edu.tw
View Replies !
Updated Column?
is there a way to obtain only the affected columns after an update query? After an update query, that involves 10 columns, I know that only 2 columns are really updated (because the values of the other cols don't change). I need to know the names of that changed cols, for logging purpose.
View Replies !
Column Privilege
I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql> grant update pass on practicedb.Acct to JohnDoe@localhost; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to JohnDoe@localhost' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column.
View Replies !
Column Heading
By default Mysql select statement displays column names header only once at the first row. I want the column names for the select statment to be displayed after every say 15 rows of display. ( This is very much equal to set line size 15 in oracle)
View Replies !
Add A Column To Get A Total
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
View Replies !
Use An Array Column
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
When To Use An Array Column?
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
Column Constraints
Currently our software product supports Oracle, DB2, and we want to see what it takes to also include MySQL. One stumbling block we have come across is that it seems that the CREATE TABLE command does not support the CHECK CONSTRAINT on a column. I know there is such a thing as the ENUM column in MySQL, but what if we want to set the constraint between two values. Example: CREATE TABLE TMPTABLE COLONE CHAR(10), COLTWO INT CHECK (COLTWO BETWEEN 1 AND 200).... Is there such a thing in MySQL.
View Replies !
Column Restruction
Is there a way of ensuring that a mySQL table column will contain one and only one true (with the remainder being false) I want to set a value of true (or '1') to a row and be assured that the remaining rows are false (or '0'). Or is this something that has to be done php side?
View Replies !
Making A Column
ihave some Access background. I've created a table for which I intended the email column to be the primary key, but didn't specify it at creation. Now, I'm trying to figure out how to specify it post facto.
View Replies !
Column Formatting
Being new to mysql I have some queries about formatting a date column 1. Can I use dd-mm-yy, my website is Australian 2. Can I leave the date field in one row empty if the date is unknown. 3. If I only know the year an event took place, can I enter just the year. 4. Would it be best if I used varchar for the column
View Replies !
Add Column With Sums
I have a table lets say Table with two colums ID and Value like the following one a 10 b 30 c 20 d 10 How is it possible with one sql statement to get the following table a 10 10 b 30 40 c 20 60 d 10 70 The 3rd column is the sum of the above element and the left one.
View Replies !
Adding To A Column Value
I have something like this:PHP Code: TABLE: sectionrating section GOOD BAD 1 23 5 2 12 10 A user can comment on a section and either the good or bad value will be incremented accordingly by a value between 1and 5. My question is how do i add the value the user supplied to the value already in the column.
View Replies !
Column Values.
is there a way i can create a column that returns result the biggest value of other columns? ie. table foo has following fields, all INT, except name VARCHAR name list A B C so if name=joe has A=5 B=7 C=9 , i want list=9 is it possible in mysql to create such variable columns? or do i need to make a perl script to update the table with the info based on what I have on A, B, and C.
View Replies !
DB Column Order
How do you change change the column order of a database in PHPmyadmin ? For example, database A is setup like ID, Name, Email, Password, Blah whereas database B is ID, email, password, name, blah I want to keep the structure of B and the data from A, but when I do the backup and try to import it, obviously, the data will be in the wrong columns so i need to change the order first, but do not know how to do it.
View Replies !
Changing Column
How can I create a query that will take values in columns and give me them as additional rows instead? For example, I need to query a table like this: id, A, B, C 1, 11, 12, 13 2, 14, 15, 16 3, 17, 18, 19 ... and get a result like this: id, label, value 1, A, 11 2, B, 12 3, C, 13 4, A, 14 5, B, 15 6, C, 16 7, A, 17 8, B, 18 9, C, 19 Anyone know how to do this?
View Replies !
|
TRACKER
