Update Function With Multiple Databases
We are looking for a way without having the possibility to install the last version of MySQL to do the equivalent of
Update t1,t2 set t1.fielda=1 where t1.key=t2.key and t2.field='processed'
meaning that we want to be able to update certains values in the table t1 depending on values coming from t2 having the same key number.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Incremental Backups Using Update Logs With Multiple Databases
I've set up my server to keep update logs for incremental backups, as described in Kevin Yank's book (Build Your Own... 3rd Edition), but I've noticed that the update.* files have entries from all the databases on my server. Is there a way to automatically split up the log files so that I can have separate update.* files for each database?
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
UPDATE Function
i need to delete the txt OLD in S_NAME but it shows error anyone :) thx in advance update Songs SET S_NAME = REPLACE () WHERE S_NAME = 'OLD';
UPDATE Function Very Slow??
I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages. Should I not expect it to be as fast as SELECT functions? $update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'"; How can I make this faster?
One Or Multiple Databases?
Just a quick question. My site uses three (potentially four or five databases) MySQLs, having no more than half a dozen tables each (one database just has ONE table). This was done because I wanted to keep different information distinct, and seperate from each other, to reduce database size, and to be somewhat more secure (if one db gets corrupted, the rest don't, heh). I've got a daily backup of these databases, the total size is about 4mb. The databases hold text, no big blob/file fields as yet. However, they do hold duplicate data, for example I've got a database for 'users' and one for 'forums', but if a user posts on a forum (to the 'forums' database), they need to pull their user data (avatar, custom sig, etc) from the 'users' database. This involves two database connections, and the usual tripe. Still in the same users/forums example, it'd be easier to have ONE database, but would this be safer/more efficient? I know that now, to retrieve a post, it's just a simple SELECT, since all the data's in the row. If they were in the same database, I'd use a JOIN using the userID in the posts table, plus the options for that post (avatar, sig, blah blah). What's the recommended practice? I can see which is easier, but would there be a perfomance hit using the JOIN (one which we'd notice, heh)? And while we're on that subject, how LARGE can a single MySQL database get? My project's fairly small, but could get bigger, I wouldn't like to have it slow down suddenly, because it's working pretty well now. Or would the differences be negligible, unless we get in the thousands of rows league?
Multiple Databases
Can anyone point me to any good documentation on how to handle multiple identical databases? We host differents websites and each has their own database, but they are all defined the same way (same tables, different data). Anybody know of good way to keep all the databases synched. Right now it kind of a pain if we need to add a table, cause we need to add it to 15 databases.
Multiple Databases
any speed increase associated with using multiple databases rather than a single database for all of one's tables? I.e. obviously speed is increased with more tables that are smaller. But is speed increased with more databases that are smaller?
Multiple Databases
I have a list of usernames and associated user_id's in a table in one database. In another database I have some data stored that is related to each user. The user_id for the user is stored with the data. Is it possible to have a query that joins the 2 and allows me to get the corresponding username alongside the users data?
One Or Multiple Databases
is there any performence-effect whith the way that tables are assigned to databases?? i.e. i have to options to eiter create several databases A, B, C and in each database i have tables x, y, z. instead i also could do only 1 database and call the tables A_x, A_y, A_z, B_x, B_y, B_z, C_x, C_y, C_z. brings me any of those options any performence advantage? I use all tables permanently all day.arranging them in different databases gives me a better systematicly overview. but then i permanently need to switch database.
Performance And Multiple Databases
Are there performance issues when a SELECT query involves more than one database? Is there any performance hit associated with specifying the database name in front of your table names? (Even if all tables come from the same DB, and if that db is the currently selected one) Is it faster to change the current db and then perform a query with "locally-specified" table names?
Multiple Databases Creation
I know that Mysql handles large database very well, but there is a project that requires more than 2000 small databases(about 20 talbes with a few rows) to be created within a Mysql server. Could somebody tell me does it make sense?
Drop Multiple Databases?
I'm using 4.1.13a of mysql. Want to know if there's an easy way to dro pall databases that share a common prefix, using a single command. So if my databases are: uesr1_coolDB1 user1_another_db_2 user1_db3 I'd like to be able to drop them all with something like: DROP DATABASE user1_*; Possible? Other ways to do the same thing?
Restoring Multiple Databases
So I used phpMyAdmin to export about 6 databases into on .sql file. All of the tutorials I've read about restoring databases talk about restoring into a current db. If I have 6 dbs in the file, what is the correct restoration command?I'm working the restore on the command line, not phpMyAdmin.I know this command: mysql -u [username] -p [password] [database_to_restore] < [backupfile] but obviosly I there isn't just one "database to restore" in my .sql file, there are 6.
Accessing Multiple Databases Thorough My Website
I am building a website at the moment, to try and run from multiple databases.I want to use two separate Mysql Datbases or one mysql and the other oracle. I want to be able to (link, relational) these togther in my website. Is this possible?
Splitting Content Up Into Multiple Databases
I have to build a system that enables users to build their own one page site with a cms. After examining what I need I figured that I need a set of tables for each user site e.g. page config, colours, layout. Since there will be many users with one or more pages if this website becomes successful I skew form the idea of putting the table sets all in one database. I think putting them all in a separate database will be a better idea but I’m not sure about this, I have never worked with more then one database.
Database Design: How Best To Manage Data Across Multiple Schemas / Databases
Our organization is looking for the best design for managing shared data. Specifically we wish to have a master table for data related to a Person. That person table would be shared by any other databases within the company that needed it. Of course, we do not want to share the whole master table, just the rows relevant to specific application that will be using it. We also want the sharing to be as transparent as possible to both the users and the support / development staff. Using a view seems like the way to go, BUT...each application has tables with FKs to the Person table. We can't do FKs to a view. We can have the application reference the view for selects / updates to the Person table, and have the other tables define their FKs to the master Person table, but we're a little worried about security since we can't narrow their permissions to only certain columns & rows like in the view. Oh, I am not doing a good job of explaining this.
Multiple Update.
I am starting with a datbase structure like this: ID | filename | info1fromfile | info2fromfile ------------------------------------------------------- 1 file1 2 file2 My query loops through the filename column and then opens each file listed in turn. Whilst open it extracts strings from the text files. These are to be entered into the rows relative to that filename. So I should end up with this: ID | filename | info1fromfile | info2fromfile ------------------------------------------------------- 1 file1 someinfo1 someinfo2 2 file2 someinfo3 someinfo4 To be able to run the UPDATE I need to know which row I am currently on. Is there a way to do that as the filename QUERY pulls out the filenames? I guess Ideally I need to know what ID we are on. Also is there an easy way to count how many filenames have been looked at so I can use LIMIT so that the UPDATE statement doesn't try to UPDATE the whole database every time. (The number of files will get larger over time so I only want to update the rows necessary!)
Multiple Update
I have two simple tables t1 and t2. I want to update the price field in t2 with the values of the (selected) price field in t1. I have followed other threads regarding similar problems and have worked out that these two attempts should work. Alas, they don't. Anybody any ideas? UPDATE t2, t1 SET t2.price = t1.price WHERE t1.item_code=t2.item_code AND (((t1.selected)= 1)) UPDATE t2 INNER JOIN t1 ON t1.item_code=t2.item_code SET t2.price = t1.price WHERE (((t1.selected)= 1)) I am pasting these directly into mysql using phpmyadmin.
Multiple Update
I'm looking at producing an application that will allow multiple users to order multiple items/ parts from what would effectively be an online store. These users could be ordering several hundred items/ parts at a time, each item/ part ordered representing one box or several hundred. Each part record will require the "total stock" to be down dated and each unit will require updated to show it as having been picked (plus other info). I'm looking at programming this in php 4.3 or php 5 using a current version of mysql. Looking around etc. I believe there are various options options open to me : 1 Create a pick list in a database table and have a "pick" routine pick the stock. This would ensure that the actual pick could only be carried out by a single process and the result of the pick request could be emailed to the user. This approach would provide the user with a very fast, responsive acceptance of an order request (but not real time - albeit almost). 2 Use Table Locks - This I understand may be the fastest but potentially could leave users with apparently hung screens, eg. if three picks were issued each taking 10 secs, then pick 3 would not start until the first two picks completed (20 secs). 3 Record Locks - I'm concerned that doing this would increase the time taken to pick the stock. But if the times were not excessive this would provide the user with an immediate "real time" response.
Multiple UPDATE
I have a txt file with around 5000 lines that look like UPDATE `members_user` SET `user_forums` = '0' WHERE `user_name` = 'user' when i go to SQL query in phpmyadmin and give it 1 of the lines individually it will take it, and when I give it 2 lines, or all of them it wont, saying there is a syntax error. I used 'find/replace' to format the file so each line is exactly the same, so obviously it wont allow me to do this command more then 1 at a time, but I cant do every single line like that, there must be a faster way.
Multiple UPDATE
i'm trying to update 3 different rows in my DB...can this be done with one query ? i tried a couple different methods, but kept giving me errors
Update Multiple Tables?
situation: a user wants to change their username on my site and their username is littered accross many tables in the DB. If in every one of those tables i've got a static "user_id" column as well as the "username" column, what is the best way to update all of the different tables with the most efficiency?
Multiple Update Of 21 Rows
I have a database table which holds price ranges for various classes of hire car and various hire periods. There are 7 different hire classes and 3 different hire periods. 21 records in total. Heres an example of what the table contains: ID......HIRE PERIOD.....HIRE GROUP....PRICE 1..........14.....................a...............25.00 2..........28.....................c...............15.00 The rows are shown in an editable form, which is basically a grid of text fields. I need to figure out the most efficient way of updating all the records when the grid is edited. Do I need to perform 21 individual updates? as I'm worried that this will be too cumbersome when there are multiple users. I thought of one way which involves having a hidden field for each row, called 1,2,3 etc, each with a value of eg. 14,a,25.00 as a comma seperated list. Then I split the post values into arrays and perform various updates. Seems a bit clunky though,
Best Way To Update Multiple Tables At Once
I need to update a few tables at once. I have a form, were I fill in some information about a concert, which updates thez concert-table. Next to this, I need to update the bands-table, the statistics-table, and some others. What would be the safest, and fastest way to achieve this?
Update Multiple Rows
I have about 20 rows that need to updated together and would rather not have 20 seperate update lines but am not sure what else to do. At the moment it looks something like this: UPDATE movies SET mon='10pm', tues='10pm', wed='11pm' WHERE movie_id='19'... UPDATE movies SET mon='9pm', tues='11pm', wed='4pm' WHERE movie_id='37'... UPDATE movies SET mon='8am', tues='1pm', wed='5pm' WHERE movie_id='19'... There is no logical order to the movie_id's they are selected from the movies table using specific criteria.
Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this: UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345 UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432 How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.
Update Multiple Rows
I have an array that I need to put into an existing db column using a perl foreach loop. I can do this using an INSERT statement but the script will be run every day and the source array will be changing as it is based on directory contents so I need to UPDATE vs. INSERT With the INSERT statment the script places each array element on a new row of the db as it should but when I try to use an UPDATE statement, I get a single array element written over and over on each row of the db. Code:
Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient). I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value. # Example UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96; # Up to 200 fields Or am I stuck with this query? (which works).
Multiple-table Update With Sum()
I have the quantities from different warehouses for each product in products_quantities, and the aggregate quantity for each product in products. I want to update products with the total from products_quantities. Why does this not work? UPDATE products p, products_quantities pq SET p.products_quantity = SUM(pq.quantity) GROUP BY pq.products_id I've tried every variation I can think of, but always get a syntax error near 'GROUP BY pq.products_id'. Can I not update one table with the sum of values from another table, in one query? I'm using 4.0.26.
Multiple Update In 1 Query
I am trying to do the following using PHP and Mysql: $sql=" UPDATE records set appear_order='2' WHERE id='19'; UPDATE records set appear_order='3' WHERE id='17'; UPDATE records set appear_order='4' WHERE id='18'; UPDATE records set appear_order='5' WHERE id='20'; UPDATE records set appear_order='6' WHERE id='16'; "; I am getting the following error message: " Cannot update records: 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 'UPDATE records set appear_order='2' WHERE id='19'; UPDATE recor" I don't know why this isn't working. when I paste this query to phpMyAdmin (I hope you guys know phpMyAdmin) - it works. somehow using a PHP code it does'nt - I get that error message. did anyone every come across this error and knows how to overcome it?
Multiple-table UPDATE.
I'm trying to update multiple-tables at the same time but I read somewhere that you could do it if you had mysql version 4.0.4. But currently, I have version 4.0.0 and I want to know how to do it with this version
Update Multiple Columns
I get an error on this command: UPDATE patchMapTable SET row=0,column=0,patchNumber=23 "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 'column=0,patchNumber=23' at line 1" row, column, and patchNumber are columns in patchMapTable all 3 are int row and column are primary keys
Multiple UPDATE Entry
I just want to update 2 or more things at the same time... but dunno how... here's what i would use:Code: UPDATE users SET location = 'changed' , password = 'new pass' WHERE username = 'david' OR SET location = 'changed too' , password = 'new pass too' WHERE username = 'brett' But that doesn't work, (it's not like i thought it would anyway)... how would i update 2 or more different fields at the same time?
UPDATE Multiple Tables Difficulties
I've been creating a stock update system that needs to be integrated with an existing database. To update the stock I need 2 tables updated in the same query (unless there is better way). In one table, items_ordered, holds every single product that has been sold and has a field StockUpdated which holds a 1 or 0 depending on whether they have been stock counted already. This will stop future queries counting stock that has already been counted. i.e. the query will look and update each record that has a 0. So far the query is able to update this table successfully.....
How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
Update Syntax For Multiple Columns
I'm looking for a feature I'm accustomed to with other SQL products, whereby multiple columns can be updated with a single SET clause, e.g.: UPDATE tab1 SET (col1, col2, col3) = (SELECT col4, col5, col6 FROM tab2 WHERE tab2.col1 = tab1.col1); I don't see this feature documented for MySQL. Is there a way to accomplish it?
Multiple-table UPDATE Does Not Work
I installed mysql-server-3.23.54a-11 and mysql-3.23.54a-11 rpms on RH9. Everything works fine. But when I try multiple-table UPDATE statement, it show me syntax error: SQL statement: mysqk> update country,city set country.cCode='A' where country.cCode = '1' and city.areaCode = country.cCode; Error message: ERROR 1064: You have an error in your SQL syntax near 'city set country.cCode='A' where country.cCode = '1' and city.areaCode =country.' at line 1
Update Multiple Rows Within One Table
I have a Dynamically created formfield. Tgis field shows the attached devices off a selected device (for instance you have a pc and in the formfields it shows two attached monitors). The monitors however are in the same database table. (with an other device_id ofcourse). The page with the pc info is shown by using the device_id in the URL. What I want to do is update some fields in this table for the pc but also for the attached devices.... When I do it like this: UPDATE dev SET .... etc. WHERE device_id IN ('#FORM.koppel_2#') It selects both device id's like this: WHERE dev_volgnr IN ('602,603') This doesn't work! However in this example the table should be updated for the devices with device_id 602 and 603. Is this possible? How would I do that?
Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin. THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN: $sql = "update table set color='red' where id='1';"; $sql_do = mysql_query($sql); THIS QUERY WORKS ONLY IN PHPMYADMIN: $sql = "update table set color='red' where id='1'; update table set color='green' where id='2';"; $sql_do = mysql_query($sql); I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.
The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people The table is quite large (300,000 rows) I need to write code that update many of the rows in the table Here is what I need to do Two of the fields that I store about each person are: location and country The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements I cannot use LOAD DATA as this is product database Is there a way to update multiple rows (each with a different value and condition in a single query?
How To Insert And Update Multiple Values In One Record
How do I insert and update multiple values in one record. e.g. 1. How do I insert "dogs,cats, snakes" in one record 2. If I have "dogs, cats, snakes" in one record and I want to update it and add 'pigs' to make it "dogs, cats, snakes, pigs", how do I do that?
Running Multiple Update Querries In Query Browser
I am trying to run multiple update statements together from the MySQL Query Browser. For example: update table1 set field1 = "123", field2 = '456' where rowid = 'abc' update table1 set field1 = "321", field2 = '654' where rowid = 'efg' I can run them one at a time, but I cannot run multiple updates at the same time. I have 1100 of them to run, so I don't want run them individually. In M$ SQL I can just put a ; or GO at the end of each line to seperate it from the next. How can I do this in MySQL?
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
|
TRACKER
