Replacing Field CITY In All Lines In A Table
table 'CUSTOMER'
I want to write the PHP code to replace the field 'CITY' with 'Lancaster' for every line in the table where 'CITY' = 'Lanc'
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Mysqldump, Entire Table In One Insert, But Multiple Lines
I'm aware of the recent mysqldump change, in that it now by default enables some optimizations. One of those optimizations is to use single insert statements, instead of separate insert statements, each on their own lines. I'm also aware of the --skip-opt / -e / --skip-extended-insert But what I'd like is a combination of having a single insert statement, but with each record in its own line, like so: INSERT INTO `article` (`id`, `title`, `text`) VALUES (1, 'test', 'this is a test'), (2, 'test2', 'also a test'); --skip-opt or --skip-extended-insert isn't it, because that'll give me: INSERT INTO `article` (`id`, `title`, `text`) VALUES (1, 'test', 'this is a test'); INSERT INTO `article` (`id`, `title`, `text`) VALUES (2, 'test2', 'also a test');
View Replies !
View Related
Random City During Query Circulation
data in myTable1 (id) name (1) Danaka (2) Jane (3) Jean data in myTable2 (id) city (1) Tokyo (2) New York (2) Chicago (2) L.A. (3) Paris (3) Lyon I have two tables like the above. The following codes produce the following results. code1 select myTable1.id, name, city from myTable1 left join myTable2 on myTable1.id=myTable2.id where myTable1.id=2 result1 (2) Jane New York (2) Jane Chicago (2) Jane L.A. code2 select myTable1.id, name, city from myTable1 left join myTable2 on myTable1.id=myTable2.id where myTable1.id=2 ORDER BY RAND() LIMIT 0,1 result2 (2) Jane L.A. //random city I like to produce my target result below. target result (2) Danaka Tokyo //random city (2) Jane Chicago //random city (2) Jean Lyon // random city
View Replies !
View Related
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
View Replies !
View Related
How To Find Nearest City By GPS Information Available
I have a database with cities list with their longitude and latitude. I need to find nearest city for some point (I also know longitude and latitude of that point). I wonder is it possible to find nearest city using MySQL? Does MySQL able to find nearest number for DOUBLE like 34.782991023? If yes, how can I use it to find really nearest city? Searching by longitude or latitude alone sound easy, but what about searching by both? It sound like hard task, because one degree of latitude is always about 110-112 Km, but one degree of longitude can be from 0 to 110 Km (depends on latitude). Ok, even if I take one degree of latitude as 100 Km and one degree of longitude as 70 km, how would you search for the nearest city for some specified point?
View Replies !
View Related
Efficient Country/Province City Setup
I currently have a program that does a global query of all of the data. I want to break it down so as to drill the user down to thier local area. for example: Country Province Region City As you can see, a country can have many provinces, a province many regions and a region many cities or a province many cities. What is the most effecinet way to do this in MySql? I'm OK with doing single tables. ie: INSERT INTO Province VALUES (NULL, 'Ontario', 'ON'); but I'm unclear if that's the way it should be done. I'm using PHP on the client side.
View Replies !
View Related
Mysql Lines Missing
Since two months and rarely (but very embarassing), some lines of the tables of my handmade ecommerce website disappear For example, I edit everything about a customer order and one week later, my program wants to take the lines again to build the invoice but they went away Today, more embarassing : I had an order from a customer by credit card (so the basket existed). I wanted to see the order but the lines of my basket are missing. This happened to a customer account as well in the past.I do not think this is hacking (SQL injection) because it is too accurate (only the customer lines are missing today).
View Replies !
View Related
Create 5,050 Lines Of Code
Small problem, and probably a quick solution. (but I have little background in programming) I need to create 5,050 different lines of code for inserting data into one table. I have various large number sets I need to generate and place into every row under one particular column value... (basically, there's one particular field that has 100 values) I'm looking for a way to write a script to generate these numbers for me that I can either copy and paste, or generate to a .txt file.
View Replies !
View Related
Limit The LAST Lines Of Select
I need to get only the last x lines from a select query (maybe 10 lines out of some 100's). How can I simulate the LIMIT clause for this purpose? My query: SELECT * FROM table1 WHERE account_id = 3 AND user_id = "username" ORDER BY mail_time;
View Replies !
View Related
4000 Update SQL Lines
Windows XP Pro SP2 (All Updates) & WAMP MySQL 5.0.45 Community NT with InnoDB tables. I am running a 4000 line update script which updates almost every field and record in a table. It seems to run successfully within one second but none of the changes seem to work. e.g. START TRANSACTION; UPDATE table1 SET Field1 = a1, Field2 = b1, Field3 = c1, ... WHERE id = 1; UPDATE table1 SET Field1 = a2, Field2 = b2, Field3 = c2, ... WHERE id = 2; UPDATE table1 SET Field1 = a3, Field2 = b3, Field3 = c3, ... WHERE id = 2; COMMIT; I am running the query under Navicat 8.08 but I also constructed this query in SQL to update from a base table and also wrote code to generate and run the SQL from MS Access. All of these seem to produce the same results - although if it is a small batch then the changes remain for about 10 minutes or until all the other batches are run.
View Replies !
View Related
Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked! I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email. The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.
View Replies !
View Related
Query Reproducing Lines When I Just Want Extra Columns
I have a problem with query returns off my query. I have a table system that has a table called symbol with symbol_id and symbol_setid (FK). These symbols belong to a symbol set which has an id that can be shared amongst symbols. e.g. SymbolId | SymbolSetId 1 | 1 2 | 1 3 | 2 4 | 2 5 | 2 When I query my table system with a whole lot of other information with this statement:
View Replies !
View Related
LINES TERMINATED BY '' Giving Lower Case N
I’m trying to write a table to a delimited file readable by excel. My query is: SELECT * INTO OUTFILE 'C:/path/filename.txt' FIELDS TERMINATED BY '=' ESCAPED BY '' LINES TERMINATED BY ' ' FROM tablename However, instead of a new line where expected, I get one giant line with a lower case n where I expect the line breaks to be.
View Replies !
View Related
Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id. How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously? My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense). Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.
View Replies !
View Related
Replacing
I'd like to replace every instance of £ with £ in a text field in my database. The field contains text and £ will be one character amongst many. Is this possible?
View Replies !
View Related
Replacing One Row With Another
This is a very simplified example of what I'm trying to do. I have two tables, A and B. Table A has one field, "id", table B has two fields, "id" and "idA". B.idA is related to A.id, and its value must match a row in table A, or be NULL. There is a cascade setting set to ON DELETE SET NULL for this field. Now, when I delete a row from A, all rows related to it in B are updated to NULL, as the related rows no longer exist. What I want to do is delete a row from A, and then change another rows id field to the value of the deleted rows id field, while keeping the row in B unchanged. So, for example, I have rows 1 and 2 in A, and a row in B. The row in B points to row 1 in A. I want to delete row 1, without setting the value in B to NULL, and then change the id of row 2 to 1.
View Replies !
View Related
Replacing A Word
I searched for the answer to this, but gave up. The MySQL manual gives a REPLACE() string function, but I don't quite get it. I need to find 'ALL-' in a my 'dept' cell of every record and replace it with 'NEW-'. I don't understand how SELECT REPLACE('www.mysql.com', 'w', 'Ww'); would do this.
View Replies !
View Related
Replacing Data
I run the query SELECT * FROM tdate WHERE desc = '3'; and that returns the following information date_id(pk) date desc amount 1 2008-01-01 3 1 2 2008-01-02 3 1 3 2008-01-01 3 1 How do I change the entry in the 'amount' column where the 'date_id' is '1'? I have tried using REPLACE tdate(amount) VALUES ('8') WHERE date_id = '1'; But it returns an error saying it doesn't like the last line.
View Replies !
View Related
Replacing Version 4.1.11
I have a Linux Box that I installed Fedora Core 4 on which came with MySQL 4.1.11. I have not yet created any tables so I just need to replace it with the newer version since I want to be able to use some of the new features. The question is - what do I need to do to - do I need to remove any files or uninstall the older version?
View Replies !
View Related
Replacing One Character
how can i replace one character in with another, in any row.. if it can be done even in any table:) like you would do 'find and replace' in a very large text document (that is the way im doing it now... export whole base, delete it, replace characters in dreamweaver and import base back...) i am doing this because of characters.. which are not part of US alphabet. i would NOT like to change encoding - it brings a lot of other problems with it..
View Replies !
View Related
Replacing An Underscore?
I am trying the following replace query but I keep getting an error. I want to replace any underscores (_) with an actual space in the column file_name UPDATE [downloads_files] SET [file_name] = REPLACE( [file_name], '_', ' ' );
View Replies !
View Related
Searching And Replacing ' '
I have a problem searching and replacing paths in my database: I want to change all paths from : ew ew1 ew2image.jpg to: /new/new1/new2/image.jpg I am trying the following SQL statement but it doesn't work. update menu set image = replace(image, "", "/"); What am I doing wrong?
View Replies !
View Related
Replacing String With Regexp
I would like to replace all strings in a table with regexp: the strings contain the substring "-na", and I would like to replace the whole table field with the original content but without the substring "-na". I know already how to find the lines that contain the "-na" with LIKE and/or REGEXP, but i dont know how to replace it.
View Replies !
View Related
Replacing Word From Fields
I have a large DB full of 98% acceptable content. I want to replace all the swears within a field. I want to select all the fields that contain, say, "f..." and replace with another word like "fudge" or something. I don't want to delete the row because it is much needed content.
View Replies !
View Related
Replacing More Than One Character At A Time
how I can remove both a space and a forward slash from a column's result on the select statement? Here is what I am trying to use: SELECT colA FROM table1 WHERE REPLACE(colA,' ','')='$variable' Now I need to use some function that allows me to remove both the 'space and the '/'. Can that be done with one select statement?
View Replies !
View Related
Replacing Access As A Front End.
We have a large number of Access MDBs floating around in our company. Our owner has decided that he will not be purchasing into the Microsoft scam, so we have a need to replace Access with an open source or otherwise free solution. I would like to use MySQL since I've just spent the past year becoming familiar with it. However, we need an easy-to-use user interface that is simple enough for people who are not familiar with SQL statements to understand. Ideally, we need something that operates like Access where you can build queries and reports in a graphical manner instead of using SQL statements. Now there are two solutions that I know will be brought up that I need to address now so that everyone knows it is not feasible: 1. Code forms, reports in PHP, etc: The problem is that we do not have a dedicated PHP programmer. In fact, I am the only person in the company that codes PHP. However, coding PHP is by no means my primary task. 2. Education: The problem is that our IT department is too small to educate the entire company about SQL statements. There wouldn't be anyone available from our department that could step away from their normal duties even for a day to train employees.
View Replies !
View Related
Replacing Values In Database
I am working on cleaning up a database. There is a field where there shouldn't be any html formatting whatsoever, but it does. Is there any possible way to get rid of html tags using the REPLACE function? For example, I would like to delete any <img /> tags and anything within them. Is that possible in SQL?
View Replies !
View Related
Replacing Server And Copying Databases
tried to start 'mysqld', the log shows the following errors: 081019 10:43:02 starting instance 'mysqld'... 081019 10:43:02 InnoDB: Started; log sequence number 0 43902 081019 10:43:02 [Warning] './mysql/host' had no or invalid character set, and de fault character set is multi-byte, so character column sizes may have changed 081019 10:43:02 [Warning] './mysql/user' had no or invalid character set, and de fault character set is multi-byte, so character column sizes may have changed 081019 10:43:02 [Warning] './mysql/db' had no or invalid character set, and defa ult character set is multi-byte, so character column sizes may have changed 081019 10:43:02 [ERROR] Fatal error: mysql.user table is damaged or in unsupport ed 3.20 format. 081019 10:43:03 angel(): mysqlmanager exited abnormally (exit code: 256):respawn ing... 081019 10:43:04 IM pid file: '/var/run/mysqld/mysqlmanager.pid'; PID: 32497. 081019 10:43:04 Angel pid file: '/var/run/mysqld/mysqlmanager.angel.pid'; PID: 3 2417. 081019 10:43:04 guardian: starting instance 'mysqld'...
View Replies !
View Related
Handle Replacing ' In Data Already Input
I have recently been given a project that belonged to another programmer. The issue I'm having is that no data validation was put in place of the program and so now I have data that has characters that are not allowed by MySQL, specifically apostrophes. I now how to do it in PHP to replace the character, but what I need to do now is to replace the data already in the table. I'm specifically talking about a user's login name. Can I escape it inside the database and not have the user's login name change? For example I have a username that is O'LearyDM. I haven't been able to comb through all the code to figure out just how the original programmer was able to even allow insertion of apostrophes, but now I need to fix it.
View Replies !
View Related
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.
View Replies !
View Related
Loading New Data By Replacing Files...
I regularly have to update the contents of my database which serves a a placeholder for the information of my website. What I do is that I work on my local PC where I have the same database installed, update this very one, and then try to update the database on the remote server by replacing the tables' files directly in the folder of my database. I chose this because I need to update all the information at once, as my web site is accesible all through the process of updating the database. Thing is the new data is not taken into account if I do not restart the WHOLE SERVER!! It is not enough to just restart mysql... How could I do this properly?
View Replies !
View Related
Replacing Tables Via A Fail-safe Method
Each month I receive a XLS file containing a customers stock list for that month, which is converted to a CSV file, and then processed into a table via PHP scripts. Rather hazardously, this process first involves truncating the previous contents of the table, with the new data placed in 'over the top'. Can you recommend a method of performing this operation in PHP which will only truncate and proceed with the changes if the new table data has first been confirmed valid?
View Replies !
View Related
Update One Table Field With Another Table Field
i am having a problem in my update function. I have two tables: buyselldata table and autozack_signal table buyselldata bsignal coloum has to be updated with Close_Price in autozack_signal table if macd_buy in autozack_signal is 1 common field in both tables are Script_Name "Update buyselldata b set b.buy = a.Close_Price where exists(select Close_Price from autozack_signal a where a.script_Name = b.script_Name and a.macd_buy=1)"; i am getting -1 for mysql_error()
View Replies !
View Related
Updating Table Field From Another Table Field
I have a field called 'tel1' in table 'live_properties' and I want to update a 'tel1' field in 'live_properties' with 'phone_num' field from 'usersTBL' MYSQL code is here but giving error any suggestions UPDATE live_properties SET tel1 (SELECT phone_no FROM usersTBL WHERE username = live_properties.username)
View Replies !
View Related
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
View Related
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
View Related
|
TRACKER
