UPDATE combined with SELECT gives headaches


Hi all,

I am trying to combine an UPDATE with a SELECT statement to change multiple rows. The query works without errors, but whatever I try, zero rows are affected (and that's not what it should do, or better, what i would like it to do).
I've searched everywhere on the Internet to find a solution but to no avail, so that's why I'm trying here.

The SELECT statements all work fine on the table when I run them seperately, with the deed results, but combined in the UPDATE, I get no results (no affected rows).

Here's the query:

UPDATE forum_article
SET childs = (SELECT COUNT(`response_to_forum_article_id`) FROM (SELECT * FROM `forum_article`) AS X
WHERE `response_to_forum_article_id` '0'
GROUP BY `response_to_forum_article_id`)
WHERE forum_article_id = (SELECT `response_to_forum_article_id` FROM (SELECT * FROM `forum_article`) AS Y
WHERE `response_to_forum_article_id` '0'
GROUP BY `response_to_forum_article_id`)

Hope somebody has a clue. The subqueries are there because I learned that MySQL needs aliased subqueries when the UPDATE and SELECT statements are affecting the same table.
Also, to explain a little bit more, the 'childs' is the column that needs to be populated.

Posted On: Friday 12th of October 2012 12:26:32 AM Total Views:  157
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Getting the last record that was updated

I want to find out what was the last record updated. If I wanted to know what the last record inserted I would use last_inserted(id). Would I use the same function
VIEWS ON THIS POST

79

Posted on:

Friday 12th October 2012
View Replies!

Simple Insert and update problems.

Im having a rather irritating problem..because its so simple and I just cant figure it out. In my database I have some company info and the primary key "companyID" is used to reference it in several other tables. however in phpmyadmin I cannot figure out how to set it to automatically generate when I create a new company in the company table and then propogate to all the tables it links too. if anyone could fill me in on how to set this up Id really appreciate it.
VIEWS ON THIS POST

86

Posted on:

Friday 12th October 2012
View Replies!

Advanced mysql update queries

i have 2 kind of fields i need to jsut add to one i need to take its current value and add +1 to whatever its vallue is. is htis possible or do i have to SELECT and take the value out of the DB and manualy do it i need to do the same sorta thing with a datetime column where i need to add x amount of days to the current datetime value of the column can this work this way
VIEWS ON THIS POST

191

Posted on:

Friday 12th October 2012
View Replies!

Conditional update question

Another baseball question: I have a table of the voting history for the Hall of Fame. This will give me a list of inducted: SELECT playerID, yearID, inducted FROM HallOfFame WHERE inducted = 'Y' I have a Master file with records for each playerID where I have added the hofYear field. I want to update this field in "Master" with the yearID field from "HallOfFame" for the records selected above. How would I do that
VIEWS ON THIS POST

114

Posted on:

Friday 12th October 2012
View Replies!

update order_header if all order_lines received (was "Need help with a query")

I have two table, order_header and order_lines, order_lines is related to order_header by order_id. I need a query that will look at all the records in order_lines and where all lines for each order have a status of "RECEIVED", it will update a order_complete field in the order_header table.
VIEWS ON THIS POST

82

Posted on:

Friday 12th October 2012
View Replies!

Ways to insert/alter/update database with contents of another database

Sorry for confusing topic title mates... But I did not know how to explain shortly what I'm after :/ Okay, I have 2 databases. I wish to copy values from the other to the other. Now, if I say I wish to copy values from db1 to db2, I wish to do it so that: if db1 does not have similair table/column, the value in db2 will remain untouched. If it does, then db1 value will overwrite value in db2. Problem is following... I can have table 'example' in both databases. In db1 there may be columns ex1,ex2 and ex3, when db2 might just have ex1 and ex2. Databases are largish, so browsing through the tables and adding columns by hand one by one with "alter table add" ... is quite hard task. Now, is there a clever way to do this I can use mysqldumb to create .sql files with commands to create the database, or phpmyadmin or write a php script, or... So I hope there's some (relatively simple) way... Unfortunately I'm not terribly familiar with SQL :|
VIEWS ON THIS POST

118

Posted on:

Friday 12th October 2012
View Replies!

showing num of new and updated records

I am so new that I don't even know if I am a noobie, noobe, newbie, newbe\t Details: database: test table: items columns: date_added, id(UNIQUE), price Problem: When adding new records, sometimes there are duplicate id's, so INSERTING would fail. After some searching, I found that I could use the IGNORE keyword to skip that entry and move onto the next. But, I later noticed that the price was being updated, so I searched some more to find that at the end of the INSERT query, I could do the following: ON DUPLICATE KEY UPDATE price='$price'"; My Query: Code: $query = "INSERT IGNORE INTO items "; $query .= "(date_added, id, item, price) VALUES "; $query .= "(NOW(), '$user', '$item', '$price') ON DUPLICATE KEY UPDATE price='$price'"; $result = mysql_query($query, $dbc) or exit(mysql_error()); $numadded += mysql_affected_rows(); Questions: 1. If adding ON DUPLICATE KEY UPDATE, do I really need the IGNORE keyword 2. if I add 10 new entries, $numadded = 10. If I try and add the same 10 entries again, but with different prices, the price is updated, but $numadded = 20 3. How could I modify this query to show: 'x' new records added 'x' records updated 'x' records were not entered (failed) Cheers and
VIEWS ON THIS POST

107

Posted on:

Friday 12th October 2012
View Replies!

Help Merging two tables with updates and delete

How do I merge two tables in MySQL with updates and delete records that are not in the new table I've looked at several other posts on this topic but they don't go into enough detail for me. None of the tables have a "primary key" or a specific unique identifier. The "real" main table has a few extra fields that are not in the update table like user input information, but I think I can manage that part. I'm a novice MySQL user, so bear with me I have a primary table and a temp table that look like this: CREATE TABLE `temp` ( `name` varchar(255) NOT NULL, `description` text NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `lastupdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `active` varchar(3) NOT NULL, `delete` varchar(3) NOT NULL DEFAULT 'No' ) ENGINE=MyISAM; I'm inserting data into the temp table like this: LOAD DATA LOCAL INFILE \'29762.txt\' REPLACE INTO TABLE temp_import FIELDS TERMINATED BY \'|\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' (name, description, active); The table format of each table is the same, but the data changes over time. Some items the data chances but the name remains constant. Some items will no longer be offered and need to be removed from the database. example: Current Database Table: ('Bob', 'Bob Description', '2012-02-17 23:32:46', '0000-00-00 00:00:00', 'Yes', 'No'); ('Bill', 'Bill Description', '2012-02-17 23:32:46', '0000-00-00 00:00:00', 'No', 'No'); ('Mike', 'Mike''s description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); ('Carl', 'Carl''s Description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); New Database Table: ('Bob', 'Bob''s Description', '2012-02-17 23:32:46', '2012-02-17 23:37:35', 'Yes', 'No'); ('Mike', 'Mike''s description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); ('Carl', 'Carl''s Description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); ('John', 'Johnson', '2012-02-17 23:35:09', '0000-00-00 00:00:00', 'Yes', 'No'); Notice Bill is not in this table, and Bob has been updated. Final Result Should be ('Bob', 'Bob Description', '2012-02-17 23:32:46', '2012-02-17 23:37:35', 'Yes', 'No'); ('Bill', 'Bill Description', '2012-02-17 23:32:46', '2012-02-17 23:39:12', 'No', 'Yes'); ('Mike', 'Mike''s description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); ('Carl', 'Carl''s Description', '2012-02-17 23:33:26', '0000-00-00 00:00:00', 'Yes', 'No'); ('John', 'Johnson', '2012-02-17 23:35:09', '0000-00-00 00:00:00', 'Yes', 'No'); Marked Bill for Delete because he was not in the new table. And updated the information in the old table to the new table. marking for delete is optional, I've been told that deleting items from database will slow it down and you should batch it and rebuild the table afterwards... Not sure how true this is. Also this needs to be as efficient as possible. I'll be working with some huge files. And it can be MySQL/PHP only code.
VIEWS ON THIS POST

102

Posted on:

Friday 12th October 2012
View Replies!

Basic problem with update using form...

I'm trying to allow for a user to update data in my database using a PHP form. PHP Code:
VIEWS ON THIS POST

140

Posted on:

Friday 12th October 2012
View Replies!

Local Database rebuild after update

I had a bug in my php code which caused undeable data to be stored in 2 particular fields in a table. I have written some code to correct the faults in the fields. The code involves Updating 2 Fields in 270 records in the same table. On completion, only 43 of the 270 records are shown In phpMyAdmin. Checking the table returns the following. Size of datafile is: 54084 Should be: 53692 Found 289 parts Should be: 270 part... After repairing the Table, records are then visible in phpMyAdmin, and my bug faults have been corrected. Any attempts to import from MySQL dump into the repaired database results in error; Can't create/write to file 'C:\Program Files\MySQL\MySQL Server 5.0\Data\sp_diary\spAddbook.MYI' (Errcode: 13) I am not well versed in MySql and would appreciate any help that can be offered. The Update Code I am using is; PHP Code: $strSQL="UPDATEspAddBookSETfacility='$facname',facilityid='$facid'whereid=$id"; $update=mysql_query($strSQL)ordie("CouldnotupdateAddressBookFacilitiesbecause".mysql_error()); The Site updates this table many times using this method with no problems at all. I can only assume it is caused by the update being repeated in a loop 270 times in short succession. Please Note, This is running on Localhost.
VIEWS ON THIS POST

132

Posted on:

Friday 12th October 2012
View Replies!

Can't remember how to update multiple rows with different values?

I can't remember how to do this: UPDATE fe_users SET tt_products_creditpoints = (2816,289,300086,303402,308637,309905,311228,311408,323580,400360,400851,405220,405591,418801,419291 ,430573,461828,471521,471777,475054,836,898) WHERE tx_lsysfeusersimport_account_number IN (2816,289,300086,303402,308637,309905,311228,311408,323580,400360,400851,405220,405591,418801,419291 ,430573,461828,471521,471777,475054,836,898)
VIEWS ON THIS POST

85

Posted on:

Friday 12th October 2012
View Replies!

Using variable in update?

how would i use a variable to differentiate between the two 'title' fields in this statement Code: update movies as mv set parts_total=(select max(part) where title=mv.title) as you can see, i'm trying to specify the first as "mv", and then set that row's "parts_total" to be the maximum "part" of all rows with the same title ("mv.title"). why does this not work
VIEWS ON THIS POST

110

Posted on:

Friday 12th October 2012
View Replies!

Select select then update

hi , i was hoping someone could help i am trying to join 2 tables then select x number of rows and then update those rows using the following code but it doesnt seem to work Code: UPDATE products inner JOIN prodcats ON products.productID=prodcats.productID SET products.stocklevel= 5 WHERE prodcats.CategoryID = 1 i want to select all the product from the products table where the category from the prod cats table = 1 then set the stocklevel value in the products table to 5 for each one. any help would be appreciated
VIEWS ON THIS POST

218

Posted on:

Friday 12th October 2012
View Replies!

Date table last updated

In phpmyadmin it shows a date and time that the table was last updated is it possible to retrieve this information
VIEWS ON THIS POST

136

Posted on:

Friday 12th October 2012
View Replies!

Please tell me good reasons to create updateable view

Could anyone please tell me good reasons to create updateable view why don't we update directly to the underlying tables instead of view
VIEWS ON THIS POST

155

Posted on:

Friday 12th October 2012
View Replies!

Automatic or scheduled Table updates

Good day, I have my MYSQL installed in windows, and i am using PHP as frontend in entering data into my mysql database. I have a Static IP provided by my ISP. I am running my MYSQL at localhost (standalone PC windows). Is there a third party tools or any program that will connect my MYSQL (standalone) to my webhost and automatically after sometime will update every tables or fields everytime i made my updates in my localhost(standalone, windows pc) Localhost(MYSQL,pc) -----(scheduled or direct)---> webhost It means, when I edit tables in my PC, after few seconds or sometime, the tables in my webhost will be updated too. Is it possible thenks and more power.
VIEWS ON THIS POST

106

Posted on:

Friday 12th October 2012
View Replies!

Simple update query will not work

I have a table like: Code: CREATE TABLE `measure2` ( `seq` int(11) NOT NULL auto_increment, `personAssignmentSeq` int(11) NOT NULL default '0', `level` tinyint(4) NOT NULL default '0', `name` varchar(255) NOT NULL default '', `value` float(16,12) NOT NULL default '0.000000000000', `periodSeq` int(11) NOT NULL default '0', PRIMARY KEY (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 I have a record as: Code: insert into measure2 values (null,1,0,'credit',10000,6) I then try to update that row with: Code: update measure2 set value = 22100 where seq = 1 and get (0 rows affected) I can update any other column but the one named "value". I have tried: Code: update measure2 set `value` = 22100 where seq = 1 to no avail. Why won't this update (using version 5.0.27-community-nt)
VIEWS ON THIS POST

133

Posted on:

Friday 12th October 2012
View Replies!

Writing a stored procedure to update three tables

How do I write a stored procedure to update three tables. Two columns have the same name
VIEWS ON THIS POST

141

Posted on:

Friday 12th October 2012
View Replies!

Insert if not update

Hi: Is there any way to insert a row if an update is not successful I mean: update table set a=3 where id=45 if id-45 does not exist then do an insert, if exist then do the update. I know the "on duplicate key" of insert, but it's not the same... any ideas
VIEWS ON THIS POST

110

Posted on:

Friday 12th October 2012
View Replies!

Select >[do math]> insert > update; syntax problem

, there is a problem with syntax, a 1064 error. Basically, what I need is: 1. select region_id and current_index fields from table named iocl_regions, where the field id equals $region_id from php ("2"); 2. add current_index to region_id and increment current_index; 3. insert the value from 2 and other php values ("98123098") into the table. I tried 2 different statements and both return errors. Code: SET @regionid := 2; SET @curindex := SELECT current_index FROM iocl_regions WHERE id = @regionid; SET @regid := SELECT regiond_id FROM iocl_regions WHERE id = @regionid; SET @optref := @regid + @curindex; INSERT INTO iocl_orders(opt_ref, iocl_ref, region_id) VALUES(@optref, 98123098)); UPDATE iocl_regions SET current_index=current_index+1 WHERE id=regid; as well as Code: INSERT INTO iocl_orders(opt_ref, iocl_ref, region_id) VALUES (SELECT (regiond_id + current_index) as curindex FROM iocl_regions WHERE id = 2), 98123098, 2); UPDATE iocl_regions SET current_index=current_index+1 WHERE id = 2; So where is the error here I
VIEWS ON THIS POST

146

Posted on:

Friday 12th October 2012
View Replies!