How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image.
The file with the new data is a .txt file, a sample of which looks like this:
v_products_modelv_products_imageEOREOR
5361453614.jpgEOREOR
5361553615.jpgEOREOR
5372453724.jpgEOREOR
The beginning part of the table looks like this:
+----------------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------+------+-----+---------------------+----------------+
| products_id | int(11) | NO | PRI | NULL auto_increment |
| products_type | int(11) | NO | | 1 | |
| products_quantity | float | NO | | 0 | |
| products_model | varchar(32) | YES | MUL | NULL | |
| products_image | varchar(64) | YES | | NULL | |
| products_price | decimal(15,4) | NO | | 0.0000 | |
| products_virtual | tinyint(1) | NO | | 0 | |
From what I've read over the past few days...I think I need to:
(a) delete the EOREOR column from the text file;
(b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.
View Complete Forum Thread with Replies
Related Forum Messages:
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
View Replies !
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...
View Replies !
Search Table Column For Existing Value
I would like to search a table's column so that I can match an entered email address against the data in that column. I'm writing an aplication for an event and I don't want duplicate email addresses in the table. I'm using PHP4.
View Replies !
How To Replace Table Column From External File
I have a table called "shipmentdetails" with a column called "shipmentdates". Assuming that I messed up all the shipment dates, and I now hold a shipmentdate.txt file that is updated, how can I replace the column "shipmentdates" in table "shipmentdetails" by loading the text file "shipmentdate.txt" ?
View Replies !
Using String For Table Identifier
Can I use a variable to specify the table I want to query in a SELECT statement? Ex: set @test_table_name = "query_table"; select * from @test_table_name; The select statement will generate an error. I looked at turning on ANSI_QUOTES mode, but didn't make any headway there. The database was designed (not by me) with certain table names stored within a lookup table. So I need to query the lookup table and append a few string characters to it to then get the name of the table I want to grab data from.
View Replies !
Inserting Data Into Existing Records Of A Table From Other Table
how to insert data from one table to an existing records of another table.... In specific how can i insert data into a table....in such a way that this data should not be inserted as a new record,rather it should be inserted into the existing records(these records at first contains only two fields of data..the remaining fields contain default values) my question is that how to insert the data into these remaining fields from another table.(more specifically what is the insert statement used).
View Replies !
Load Data :: Into Existing Table
how i can put the contents of a .sql file into an existing table? The script is by php, i give the user 4 options. When the user choose option 1 then the contents of data1.sql has to be inserted in a mysql table. so something like: PHP Code: mysql_query("SELECT contents FROM data1.sql AND INSERT INTO table");
View Replies !
Import Data Via Ssh Into An Existing Table
I want to import data from several .sql files into an existing database, into one table. I tried using the following command: mysql -e [sqlfile] [db_name] But the problem was when I imported the next file, it would overwrite the previous imported data. Could anyone let me know how to do this correctly, please?
View Replies !
Append Existing Data In A Table
I am working on an existing osCommerce store. There are over 200 products, each of which needs the associated image file name changed. Rather than do this manually (any number of ways), I am unsure if there is a query I can run to save me lots of aggravation. What I would like to do is update whatever is in the field and change it to add the same 5 characters: The Table is Products The Field is products_image Examples of existing data in this field are: abcdef.jpg ght.jpg fire_777.jpg oops.gif (the replacement image is a jpg) The result I seek is: abcdef_th.jpg ght_th.jpg fire_777_th.jpg oops._th.jpg Logic tells me to start at the end of the data, remove the last 4 characters (.jpg) and append 5, in this case: _th.jpg Could someone please give me a hand with this? I have spent many days redoing the image files and they are nearly ready to go into a live store.
View Replies !
How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a great deal of trouble exporting a text field, possibly because it had control characters that interferred with my field delimiter (also it was from a Mac, and I'm new at this.) So I have a table called invoices and another table called tasks, that has only an invoice number and the work billed on that invoice number. To see the work billed along with the other invoice data I have to do a join, and this works but is clumsy for me. Since tasks.work is a text field of 'infinite' variety I don't see the sense of keeping it in a separate table like I would with clients' info; it belongs *in* the invoice table, not just with it. Is it possible to import this text data into a field in the invoice table? I've messed around with this but it always appends the data as new records. Perhaps I was doing an insert back then. Is the answer something like 'load data infile 'workdone.csv' into invoices (workdone);' The invoices table was filled in a certain order (by invoice number) and the workdone.csv was exported in the same order, so each invoice row should get its correct text. Is this a good thing, or am I thinking too 'flat-file-ish'?
View Replies !
Inserting 100 Rows Of Data Into An Existing Table
I want to import 100 rows of data from an Excel spreadsheet into an existing MySQL table. Both the spreadsheet table, and the MySQL table have the same exact format & headers. In MySQl Query Browser, I can locate only the Edit function, which seems to only allow me to type in the data line by line. Can I import the data in MySQL Query Browser, or do I need another product? And if I can import, how do I do this?
View Replies !
Import Additional Field Data To Existing Table
I've got my data all set-up in a mysql table and all functioning well. We have decided that we need some additional bits of data for each record and we have that data in a csv file. We've created the fields in the mysql table. In the csv file, I have the userid (to match that in the current sql table) and the additional data with the column names in the csv file matching exactly the new fields in the sql table. The additional fields in the original sql table are empty, so we can just simply write-over these fields with the data from the csv file. But...we are having problems with the import using the MySql administration tool on our server. If I select a csv file upload, I get a number of fields is not same error If I use csv load data upload, it just overwrites the first few lines of current data and doesn't put the new data in the correct fields. Can anybody help, please? If csv load data is the correct format to use, what do I put in the import tool for these variables (given that I have a standard csv file that has been created using excel) Replace table data with file (yes/no default is no) Ignore duplicate rows (yes/no - default is no) Fields terminated by(default is Fields enclosed by(default is ") Fields escaped by(default is ) Lines terminated by(default is auto) Column names (default is blank) Use LOCAL keyword (yes/no - default is yes)
View Replies !
Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using: *********** SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC *********** I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Replies !
Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table? for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0. i don't want merged results. i just need to list all therapist based on the where criteria from a different table. these two tables have the therapist_id in common.
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
With INSERT Can I Increment An Existing Value In A Column?
I have a table of addresses and a seperate table with contact names - All addresses tie to one or more names - I would like to keep track of the number of names 'belonging' to an address and have thus included a column in my address table called num_of_contacts. Everytime I add a new contact, I would like to increment the num_of_contacts column in the address table. Is this possible? Me thinks not (or at least, my attempts so far have failed me) so I'd appreciate it if someone could tell me if I am wasteing my time trying and instead SELECT the record, increment it, then UPDATE it.
View Replies !
Alter Query For Existing Column...?
Im executing my 'ALTER query to add a COLUMN in a table, for which the column specified is already exists. So when i execute the script it throws an error as 'Duplicate column'. how to check for 'COLUMN' IF EXISTS and if the column specified does not exists, my alter script should be executed. Can it be done using stored PROCEDURES.
View Replies !
Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field. I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have. Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:
View Replies !
Finding Sort Order For An Existing Key Column Associated With An Index
I am trying to find within the INFORMATION_SCHEMA tables the Sort Order (ASC or DESC) of a specific Key Column associated with an existing Index. I thought maybe KEY_COLUMN_USAGE might have it but unfortunately it doesn't from what I read. It has ORDINAL_POSITION but that only lists the position of that column against other key columns for a given index. Anyone know where I might find this information or if this information is not found in the INFORMATION_SCHEMA tables? Parsing the index DDL is not considered an option for me at the moment.
View Replies !
REPLACE A STRING IN A COLUMN
I HAVE A TABLE "PRODUCTS" WITH THE COLUMNS "IDPRODUCT" "PRODUCTNAME" "PRICE" AND "DESCRIPTION". IN LOST OF THE ROWS THERE IS A STRING "AMARILLO" (MEANS "YELLOW" IN SPANISH) AS PART OF THE VALUE IN THE COLUMN DESCRIPTION. I MUST CHANGE IT TO ENGLISH (TO "YELLOW"). HOW CAN I REPLACE THOSE STRINGS WITHOUT CHANGING THE REST OF THE VALUES FOR THAT COLUMN. EXAMPLE COLUMNS IDPRODUCT , PRODUCTNAME , PRICE , DESCRIPTION ROW 1 105 , VARIGWOMENXLY , 45 , TSHIRT AMARILLO FOR WOMEN. ROW 2 149 , VARIGMENXLY , 45 , TSHIRT AMARILLO FOR MEN. ROW 3 175 , LANWOMENXLY , 45 , TSHIRT AMARILLO FOR WOMEN. ROW 4 182 , LANMENXLY , 45 , TSHIRT AMARILLO FOR MEN. ROW 5 196 , DELTAWOMENXLY , 45 , TSHIRT AMARILLO FOR WOMEN. ROW 6 205 , DELTAMENXLY , 45 , TSHIRT AMARILLO FOR MEN. ETC IT SHOULD UPDATED TO: COLUMNS IDPRODUCT , PRODUCTNAME , PRICE , DESCRIPTION ROW 1 105 , VARIGWOMENXLY , 45 , TSHIRT YELLOW FOR WOMEN. ROW 2 149 , VARIGMENXLY , 45 , TSHIRT YELLOW FOR MEN. ROW 3 175 , LANWOMENXLY , 45 , TSHIRT YELLOW FOR WOMEN. ROW 4 182 , LANMENXLY , 45 , TSHIRT YELLOW FOR MEN. ROW 5 196 , DELTAWOMENXLY , 45 , TSHIRT YELLOW FOR WOMEN. ROW 6 205 , DELTAMENXLY , 45 , TSHIRT YELLOW FOR MEN.
View Replies !
Replace Characters In A Column
I have a query that needs to be created that will replace a string with only the last four characters of the string. example column (example) has data like this, abcdefgh, dkieaaaa, ieiebbbb I would like for the column to be updated with efgh, aaaa and bbbb This is on mysql 3.23.58 and the data set is 9 million records. I'm looking for the most efficient way to do this with a sql query rather than doing a lot of php coding.
View Replies !
Find And Replace In A Column
I have a table of 2000 records with a column called 'images'. These images all end with a .tiff extension. I would like to change them to .jpg... Can I do a pattern matching and update all of these? How do you do that in mysql?
View Replies !
Replace Values In One Column All Rows
I need to replace all values in one column with the values from another column - example: I want to replace all the values in the Username Column with all the values in the EmailAddress column - all rows to be effected.
View Replies !
Update One Column Based On Another
I have two tables. One set up like this: id item_id group_id userid outcome settled The second is userid yes no I would like to look at the first table and find all rows that have a specific group_id, then update the userid yes/no based on the outcome on that row. Outcome Y then add 1 to the yes column of that userid, outcome N then add 1 to the no column of the userid. Is this possible in one SQL statement?
View Replies !
Increment Based On Another Column
I'm having a little trouble working out the best way of doing something. I'm storing a key split across two fields, the first field is 3 letters, the second is three numbers: CREATE TABLE table1 ( id_a CHAR(3) NOT NULL, id_b SMALLINT(3) NOT NULL, ... ); What I'd like to do is have it so that when a new row is entered 'id_b' is calculated as MAX(id_b) + 1 WHERE id_a = new_id_a. Is it safe to do this as some sort of nested query or is there a better way?
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
Adding New Column In A Table With Data From Another
I have been trying to accomplish the following aince quite some time.. I have tried various combinations of Union, Merge, Insert.Select, etc.. Tables: table_1 with 22 data columns, and a autoincrement primary key column. table_2 with 1 column (newtimenumber in query below) with double values, no primary key. (Although i can generate it) Relation between tables: The number of rows in both the tables are same, and if required can be mapped on one to one basis. (Not yet done as i have not assigned primary keys to the table_2; but row 1 of table_1 corresponds to row 1 of table_2) The table_2 is generated by performing a set of calculations on one of the columns in table_1; hence they correspond to each other. Problem: I want to insert the all the rows in table_2 into a new Column (newtimenumber in query below) in table_1; but maintaining the corresponding row by row relation. Tried Insert..select : INSERT INTO dbname.table_1 (newtimenumber) SELECT swapper.newtimenumber FROM dbname.table_2 this did not work, and generated new column in table_1 but the rows were appended at the end. I would be very glad if some one could help me with this. I have tried many things, but i am not getting something right.
View Replies !
Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this: SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100; In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Auto Increment Based On A Column Value?
I did a bit of searching but couldn't find anything, so: If I have a table structure similar to this: Code: table: productImages idfilenameproductIdorder 110234.jpg121 213702.jpg122 323674.jpg123 498373.jpg151 544126.jpg152 Would it be possible to create an auto increment value in the 'order' column, based on the 'productId' column? As an example, if I insert another record with filename = 12345.jpg and productId = 12, then it would set order = 4 based on the previous three entries for that product?
View Replies !
Convert Existing Data
i am working on a project and i need to convert already existing data into the mysql date format. I was told for the project that the date format was necessary. my first question is what is the advantage of having the mysql date formate which goes like this yyyy-mm-dd. the format my data currently is in is like this mm/dd/yyyy. The second question is how to convert from mm/dd/yyyy to yyyy-mm-dd
View Replies !
Edit Existing Data
I have just started to learn MySQL and this question might be silly. I have a MySQL table with user information. I want to edit this table in a website. I need to load existing data in a HTML form and then edit the fields and update them. I searched the net but could not find anything .
View Replies !
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!
View Replies !
Column That Calculates Average Based On Other Columns?
is it possible to have a column called "average" that adds up and then averages out the data in other columns? I've got a database with several columns of scores. I'd like to have a column that holds the average score. Then on my website, my visitors can sort by the highest score and I'll be able to pull the info straight from the db.. furthermore, only certain columns hold scores, they're named like "scoreA", "scoreB" and so on... is this at all possible? If not, can anyone point me to some php resources that will allow me to calculate hundreds, perhaps thousands of scores (on the fly) and present them from high to low (or low to high)?
View Replies !
How To Insert A Calculated Value Based On Diff Column
let's say I have 3 columns create table test1 ( id int, dateCreated timestamp default current_timestamp, localDateCreated datetime); the localDateCreated is always 8 hours ahead dateCreated. so when i insert id number, lacalDateCreated will be calculated based on dateCreated. How to do that? I thought trigger might help, but it gave error mysql> delimiter | mysql> create trigger trgtest2ai after insert on test2 -> for each row begin -> update test2 set local=new.ts where id=last_insert_id(); -> end; -> | Query OK, 0 rows affected (0.03 sec) mysql> insert into test2 set id=10; -> | ERROR 1442 (HY000): Can't update table 'test2' in stored function/trigger because it is already used by statement which invoked this stored fun ction/trigger. mysql> delimiter ;
View Replies !
Restrict User Permissions Based On Column Value
I have a client who wants me to make a web database engine for a softball organization (many teams). he wants him to have administrator rights, and the coaches the ability to update only their roster. The catch is as the players mature and get older their profile will need to move to the next coach. For display purposes (using PHP) I will be doing something like ... "WHERE `team` = '14U'". I want to set it up so only coaches may update their profile if `team` = "14U" is this possible to do within one table? I have PHPmyAdmin so it should be very easy to checkmark permisions on/off. An aside, but I will be doing the database administration via phpmyadmin & via MS Access, which I am almost positive shall work, I believe I've done this before.
View Replies !
Quoted Identifier
I was trying to name tables in a hierarchical way : I thus tried to use quoted identifiers and I found that I couldn't see tables which I named with a ':' in them. create table `a:b` (col int) create table `a!b` (col int) show tables the show table shows `a!b` but it doesn't show 'a:b` Moreover I know `a:b` has been created somewhere because I can insert, update, select on it ...
View Replies !
Unique Identifier
I wanted to create a unique identifier field in a mysql database, this is a fairly common practice in MS SQL, is there an equivalent in mysql?
View Replies !
Join Or Combine Two Tables Based On Unique Column
I use MySQL query browser and have made two tables in one database: First table: Test Column names: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)) Second table: Hashtest Column names: Filename (Varchar(255)), RowNumber (Integer), MD5 (Varchar(255)), SHA1 (Varchar(255)), Pad (Varchar(255)) Of both tables the column RowNumber is the primary key. Also, in both tables the column Pad is the same (the same content, not exactly in the same order) Now, I want to combine the two tables into one table, based on 'Pad' I suppose I have to use the Script function of MySQL query browser. Please explain to me what I have to do to combine the two tables into one table. This third table has this columns: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)), MD5 (Varchar(255)), SHA1 (Varchar(255))
View Replies !
Append Data To Existing Cells
I have an existing table. I need a way to append data in one of the columns. How do I append data in cells so I do not overwrite the existing data already in the cells in the column? The fields in my table are `Source` , `Theswords` , `Topic` , `Subtopic` , `References` The table name is ViewNew The column I want to append is Theswords
View Replies !
Maximum Identifier Lengths
The table contains all sorts of identifiers. I was wondering what the maximum lengths of (named) constraints are (here symbol). CONSTRAINT [symbol] PRIMARY KEY CONSTRAINT [symbol] UNIQUE [INDEX|KEY] CONSTRAINT [symbol] FOREIGN KEY Is it 64 as well???
View Replies !
Amending A Hierarchical Structure On Existing Data
I want to amend my table such that the following... SELECT * FROM words; +-------------+------+------------+ | root/parent | pos | word | +-------------+------+------------+ | abandon | Verb | % | | @ | @ | abandon | | @ | @ | abandoned | | @ | @ | abandoning | | @ | @ | abandons | | abbey | NoC | % | | @ | @ | abbey | | @ | @ | abbeys | +-------------+------+------------+ would instead read... SELECT * FROM words; +--------------+------+------------+ | root/parent | pos | word | +--------------+------+------------+ | NULL | Verb | abandon | | abandon | Verb | abandoned | | abandon | Verb | abandoning | | abandon | Verb | abandons | | NULL | NoC | abbey | | abbey | NoC | abbeys | +--------------+------+------------+ Thus dispensing with the need to rely on the table's natural order, but I'm a bit stuck as to how to go about this. I'm happy to add a numerical key to the table if it makes this process easier. In my mind (not an especially logical place) the logic is something like this: if parent is not "@" set var_parent = parent and var_pos = pos else set parent = var_parent and pos = var_pos finally delete from words where word = "%" But how do I turn that into something useful?
View Replies !
|