2-table UPDATE
I am running the following query through PHP's mysql_query:
UPDATE hotel, hotel_brand
SET hotel.hotel_brand_id=0, hotel_brand.hotel_brand_parent_id=0
WHERE hotel.hotel_brand_id=6 AND hotel_brand.hotel_brand_parent_id=6
In actuality, it's two queries combined into 1. I am deleting a value that rows in these 2 tables reference, and want to set the values to 0.
Of course, the problem is the values aren't changing to 0.
Is splitting these up the best solution?
View Complete Forum Thread with Replies
Related Forum Messages:
INSERT Into New Table, SELECT From Old Table, UPDATE Old Table With New Key
Code: INSERT INTO retailers (retailername, retailerdesc, retailerwebsite, retailerurl, active) SELECT datasource_retailername, datasource_retailerdesc, datasource_retailerwebsite, datasource_retailertrackurl, 1 FROM datasources_retailers_idx AS i LEFT OUTER JOIN retailers AS r ON r.retailername NOT REGEXP REPLACE(i.datasource_retailername, ' ','.+') UPDATE datasources_retailers_idx SET idretailers=last_insert_id(); Here's what I'm trying to do: * Select from old table * check if there is a matching retailer in the new table * If not, insert retailers details into new table * Update a reference column in the old table with the INSERTID primary key value of that row from the new table
View Replies !
Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows: Customer table contains: 1) customer_email_address 2) customer_newsletter (value 0 or 1) Visitor table contains: 1) email The visitor table contains email addresses from customers that have signed up through another system. I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.
View Replies !
Update Table With Info In Other Table
i have a case like this: table table1 (key, accumulator) table table2 (key, counter) i want to, for each table1.key = table2.key, update accumulator with the info in counter, something like this: update table1 set accumulator=accumulator+table2.counter where (? counter is the value related with the same key as the one in table1) how do you write a sentence like this one?
View Replies !
Update A Table With Data From Another Table
I have two tables with similar data. The firs table contains data that is to be updated with data from the second table. The first table (tblA) has a unique key, but the second table (tblB) does not. I have to use the 'lastname', 'firstname' and 'dept' fields that are in both tables and join the tables on those three fields. I have tried: update tblA, tblB set tblA.empPty=tblB.empPty where ((tblA.empLName=tblB.empLName) and (tblA.empFName=tblB.empFName) and (tblA.empDept=tblB.empDept)); with some test data where I know I have a match using the three fields, but nothing gets updated.
View Replies !
Update Table With API
I'm trying to develope a getway between matlab and mysql. I would like to write the result of matlab routine into mysql table without "UPDATE .....SET..." statement, because I've have to write a different value for each row and I have to write a lot of row. I would like to write table row by row sequentially.
View Replies !
Update Table From TXT/CSV
I have a table With Field1(INT,8,PK), Field2(INT,6,PK), Field3(VARCHAR,255) and Field4(VARCHAR,255). I have to update records from a FIXED LENGTH TXT that contains: Field1(8 digits)Field2(8 digits)Field4(1-255 digits) or the same in CSV....
View Replies !
UPDATE Table SET
Does anyone know how to implement this into a php form CORRECTLY? I have all the proper syntax but then the server gives me a message that says the mysql version may not go along with the syntax. How do I work around this then? I need to update query strings and such and nothing will work.
View Replies !
UPDATE From One Table To Another
This should be so simple but I'm getting a very strange error?? I'm using MySQL version: 4.1.12-log I've got an outdated country table that I'd like to update info from an ISO table. Pretty straight forward if you ask me. Here's the query: UPDATE country, isocountry SET country.un_numcode=isocountry.numcode WHERE country.country_code=isocountry.iso; I created a column called un_numcode and then I'd like to put the iso numcode info in my table where the country_code = iso code (same data, different field names in different tables). It goes along really well for 38 rows out of 239 rows?? And then I start getting 127 in ALL the un_numcode fields even though that value does NOT exist in the country.numcode table!
View Replies !
Update One Table
I have 2 tables one for the team standings one for the scores, this select statement will calculate win loss : SELECT SUM((homescore>awayscore and home=teamid) OR (awayscore>homescore and away=teamid) ) as wins, SUM((homescore>awayscore and away=teamid) OR (awayscore>homescore and home=teamid) ) as losses FROM scores, teams order by teamID
View Replies !
Table Update
How do I relate such a way that I update a column of the main table, another secondary table's column related to the main table gets updated automatically?
View Replies !
UPDATE From A Linked Table
I wanna update the table COMPANY from a linked field. The following is not accepted. Could anybody help me with what the command should be? Thanks a lot. UPDATE `COMPANY` SET `COMPANY`.Region_ID = ( SELECT `Area`.Region_ID FROM `AREA` WHERE `COMPANY`.Area_ID = `Area`.Area_ID )
View Replies !
Update A Table With Values In Another
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y column_z How do I update the rows of tableA that match rows of tableB (all rows of tableB are unique) What I want to acheive is a check against tableA.column_x with tableB.column_x, if they match, then update the row in tableA.column_y with the value of tableB.column_y and tableA.column_z with tableB.column_z
View Replies !
Update Table By ID Ranges
How do i use the UPDATE statement to update a range of Primary Key id numbers. (let say 100 through 1000). Example, UPDATE testTable set testField='9999' WHERE test_id in (100:1000);
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Automatic Table Update
i have an database in which i need to update a status filed of an table with the date. i,e those datas which gets expired are to be updated.. so need to run a command in mysql by default.,
View Replies !
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
View Replies !
Update Table Problems
I'm trying to update a table where specific values are in another table. Here is the update statement I am using: UPDATE Route_Detail,Routes SET Route_Detail.Trip_Calc='S' WHERE Routes.Route_ID = Route_Detail.Route_ID AND Routes.Project_Code = 'OSUT' AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006 This statement runs but does not update any rows. This select statement returns 11 rows: SELECT RDetail_ID FROM Route_Detail INNER JOIN Routes ON Routes.Route_ID = Route_Detail.Route_ID WHERE Routes.Project_Code = 'OSUT' AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006 How do I get the UPDATE statement to update those 11 rows. I'm using MySQL 4.1.21-standard version.
View Replies !
Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't. ID int(10), std varchr(50), atd varchr(50), dtd varchr(50), ctd varchr(50) I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also. If I change to another record it may all work or not. Any Ideas at all?
View Replies !
Update Table With Java
im trying to update my table bunt int test=5; String update="Update bunt SET buntnr='test' Where bunt_ID ='ver.bunt_ID'"; wich results in this error Data truncation: Out of range value adjusted for column 'buntnr' at row 1 If i use this, changing variables to Ãnt it works fine! String update="Update bunt SET buntnr=5 Where bunt_ID =1";
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Update Records In Table
i have installed a mysql server, and i have a problem with import record in database. the origine is a file txt or csv, the destination database contain also other records, and i must update the records into database
View Replies !
Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field. I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:
View Replies !
Very Slow Table Update
I have two tables. One is really a subset of the other. However, they came in different data files and I would like to pull data from one and put it into the other. However, it is VERY slow! Once the tables are setup I will only read from them and perform operations. I will never update or insert. However, I can't get things setup to that point. Code:
View Replies !
Update Table From File
I've a file with 6000000 UPDATE commands. I'm planning to execute those commands on BANKING database using: mysql banking < update_table.txt Is this the best way to execute many updates on the database, without affecting performance? I'm worried to overload the BANKING database with those long updates... What do you think about it?
View Replies !
UPDATE A Table, Using GROUP BY
I have a table with the following fields -- schoolcode (e.g., 313) teachername (e.g. JONES) studentname (e.g., JOHNNY DOE) totaltestitems (e.g., 50) totalitemscorrect (e.g. 40) studentpctcorrect (e.g., 80.0) teacherpctcorrect (????) (NOTE: schoolcode + teachername combination would be unique.) I want to UPDATE the table with the average percent of items correct for students assigned to each teacher (teacherpctcorrect).
View Replies !
Update Count From Another Table
I am trying to write a query that takes the count() from one table and updates a second table with the value. This is what I have so far: UPDATE wp_posts SET wp_posts.comment_count = SELECT count(comment_ID) as comment_count FROM wp_comments where wp_comments.post_ID = wp_posts.ID What am I doing wrong?
View Replies !
Daily Update Of A Table
I've a MySQl db. I've a table "example", with 3 columns: code::int date:date status:bool (0 or 1) I need each day something checks my table and calculates difference between current date and date in the second column of my table. ....
View Replies !
Update, Table Locking
Is it possible to have update in a iterative loop with start index and range wtih the query? I wouuld like to have the update happen in steps with loop to avoid getting the entire table locked. Also, I do fine the query takes around 10.88 secs to update to complete. this is one reason i prefer to have update happen in steps so that entire table locking do not happen. its innodb table.
View Replies !
Update Values In Another Table
Table one has columns thread_forum_id and thread_thread (and a bunch of other stuff) Table two has columns topic_id and post I would like to populate thread_thread with the data from post however the columns thread_forum_id and topic_id need to correspond to each other when i do this. for instance if topic_id = 1 and post = A and another row is 2 and B then i want the resulting table one to have 1 and A 2 and B but not 1 and B or the other way around.
View Replies !
Multiple Update Same Table
I have a table that i need to have only 1 field in the same column be yes and the rest turned to no... I have tried SELECT * FROM tab1; UPDATE tab1 SET 'field' = YES WHERE id = 5; UPDATE tab1 set 'field' = NO WHERE id != 5; Obviously running two at the same time does not work but if i can somehow combine these two to do it all at once...
View Replies !
Update A Big Online DB Table
I have a big MySQL table with about 3000000 records (size 800MB), this table is updated twice a week with about 120000 new coming data and some old records need to be removed. Ideally, I handle the update (with some bash/Perl scripts) on my development server where I do lots of post-processing[i.e. field validations] which can not simply done by MySQL update statement. And then I upload the new table to the production DB servers, and use the following command-line to update the table: mysql -umyname -pmypassword dbname < mytable_dumped_from_development_server.20090311.sql This works well except it takes a long time (about 10 minutes) to finish, and during the updating, the database is locked and the website is therefore frozen. I know I can write a script [use mysql REPLACE INTO, instead of the above command line] to directly update the table on the production server, at least no long time table LOCK although it's more risky. Is there a best practice or common method to update a large live MySQL table[usually twice a week] and meanwhile take less impact on the website. my system is: MySQL 5.0.22 and RHEL-5.
View Replies !
Table Locking For Update
I want to develop a multi-user application using mod_perl 2 with MySQL 5.0. I have been experimenting via the comaand line mysql interface with row level locking. If in one window I do SELECT * FROM IMAGES WHERE IMAGE_ID = 135 FOR UPDATE; and then in a second command window I do UPDATE IMAGES SET IMAGE_LOCATION = 'XXX' WHERE IMAGE_ID=135; MySQL allows the second update to execute and updates the record. I had expected the seond statement to return an error indicating that the row was locked by another user.
View Replies !
Triggers To Update Another Table
I see there is a limitation with triggers about refering to table names. So if I wanted to have a trigger on 1 table to update another table, I'm assuming this isn't possible? Bascially it's a reload project. When one table's flag is set to released, I want to add a record into a Reload table, that has some record information from the updated table. A process is reading the Reload table and when it has records it reloads some data based on the records.
View Replies !
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.
View Replies !
Update Same Table With More Than One Record
I have a forum table which I need to perform the following update 1) I have an original message which I need to change its online status into DISABLE 'D' in my "status" field as follow single command: UPDATE forum SET status = 'D' where message_id = 1 and parent_id is NULL; *the parent_id field is NULL because it is a parent thread... However, it has some child threads to this message...therefore I must force the all the child thread to change the status into 'D' also. So I did a second sql query as follow: UPDATE forum SET status = 'D' where parent_id = 1; *parent_id = 1 means it points to the parent message_id = 1 My question is DOES anyone know a good sql query that will combine these two queries into ONE single query?
View Replies !
Update A Table Being Selected?
Is there a relatively easy way to fix this bad decision I made long ago? I created a table thusly: CREATE TABLE `blah` ( `mod_date` date NOT NULL default ��-00-00', `mod_time` time NOT NULL default ༼:00:00', ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I added a new field to the table that looks like: `modified` datetime NOT NULL default ��-00-00 00:00:00' I thought I could update it with: UPDATE blah SET modified=(SELECT CONCAT(mod_date, ' ', mod_time) FROM contacts); But a search in this forum showed that one can't UPDATE a table that is being SELECTed from. There was mention of a temp table (and perhaps JOIN(?)), but further searching has left me wanting.
View Replies !
Using Import To Update Table
I'm working on a business directory site that I created using phpmydirectory. All of my data was imported into mysql from the admin area of my site. My category table has ID, Title, Description, and Keywords fields. At the time that I imported my business listings, I left the Description and Keywords fields blank. I am now wanting to go in and add descriptions and keywords to each category. I would like to setup a simple spreadsheet and fill in the description and keywords for each category, and then use Import in PhpMyAdmin. Is this possible? If so, how would I set it up? Am I making sense? I really have no idea on how to use PhpMyAdmin. I can browse around and export when I need to, but I'm scared I'll screw something up if I try to update a table.
View Replies !
Update Monthly Table With Daily Max / Min
I have 2 tables, they are daily and monthly tables. I want to update the monthly table with the max and min value of the daily table with the corresponding month. I figure out the following sql but it doesn't work because the group by function seems not supposed to be allowed. update MONTHLY as m SET m.M_HIGH=max(d.D_HIGH), m.M_LOW=min(d.D_LOW) WHERE m.MONTH=(select MONTH from DAILY as d GROUP BY d.MONTH); How can I make it in 1 sql statement ?
View Replies !
Update Performance On Large Table
I have a table that is approaching 4 gigs. I have optimized as much as I can with indexes so that select statements are ok, but updating entries seems to be taking a bit of time. I have 2 questions: 1) I know about the EXPLAIN command for select queries, but is there something like that for updates? I get an error when I try to do "EXPLAIN UPDATE mytable..." Do updates use indexes the same way as selects? 2) Would breaking the table up into seperate tables and then using a merge table increase the performace? I've heard that key reads are slower on merge tables - but since I could make 4 tables of a gig each, wouldn't it be faster opening up 1 smaller file to make an update instead of a huge one?
View Replies !
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
View Replies !
You Can't Specify Target Table '' For Update In FROM Clause
I have problem deleting rows from table with subquery referring to the same table. I store tree structure in the table (there is ParentID field for each row that specifies primary key of the parent row). I'm trying to delete all children of the given row which doesn't have their own children. I can't do this from the script by iterating through all rows because there could be thousands of such records. I thought that the following query will do that: DELETE P FROM `tree` AS P WHERE P.`ParentID` = @parent AND NOT EXISTS (SELECT * FROM `tree` AS C WHERE C.`ParentID` = P.`ID`) But this doesn't work, throwing error: You can't specify target table 'tree' for update in FROM clause How can I make this work?
View Replies !
Scheduled Update Of Table Element
I have a MySQL db running on a server with a dynamic ip, and I keep a copy of the external IP in a table. Is there an easy way to make a script that will run on a schedule to check the external IP of my server and update the table element to match?
View Replies !
Update Whole Table With Unique Data Per Row
This is my first post and I am fairly new to MySQL and PHP. I need to update an entire column of data with a column of data from another table. Each row is a price attached to a specific product. When I do an Update/Select, I get an error saying more than one row is being returned. Is there a way to do this using MYSQL alone, or do I need to use PHP and and a loop with individual inserts per row?
View Replies !
You Can't Specify Target Table '...' For Update In FROM Clause
I have a table called acct that contains the following fields among others: id,acctid,value,processed Processed=0 Not processed Processed=1 Currently processing Processed=2 processed In need the ability for a program to check to see if a particular acctid has been processed, if it hasn't it sets Processed=1 processes the account then sets Processed=2 It is common that there will be multiple records for the same acctid and I only want each acctid to be processed once. I through this would work but I got the error at the bottom. UPDATE acct SET Processed = '2' WHERE acctid = ( SELECT acctid from acct where Processed = '2' GROUP BY acctid) #1093 - You can't specify target table 'acct' for update in FROM clause
View Replies !
|
TRACKER
