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 1 Replies (Posted: January 25, 2009 04:23AM)
Sponsored Links:
Related Forum Messages for MySQL:
Codeigniter - Database - Update Multiple Tables With A Single Update Query?
I saw this on the codeigniter forum Considering the below code UPDATE a INNER JOIN b USING (id) SET a.firstname='Pekka', a.lastname='Kuronen', b.companyname='Suomi Oy',b.companyaddress='Mannerheimtie 123, Helsinki Suomi' WHERE a.id=1; This is how you would apparently do it in Codeigniter $this->db->set('a.firstname', 'Pekka'); $this->db->set('a.lastname', 'Kuronen'); $this->db->set('b.companyname', 'Suomi Oy'); $this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi'); $this->db->where('a.id', 1); $this->db->join('table2 as b', 'a.id = b.id'); $this->db->update('table as a'); this does not work in reality. I have had a look a the SQL which this produces and the results do not even mention the join.
Posted: Jan 28 at 16:07
View 2 Replies!
View Related
Update Multiple Columns With A Select Returning Multiple Rows?
I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table: postcode nearestPostcode1 nearestPostcode2 nearestPostcode3 _______________________________________________________________ KY6 1DA - - - KY6 1DG - - - KY6 2DT - - - KY6 1RG - - - .... I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated: update table1 set nearestPostcode1 = (select query for returning the first nearest postcode), nearestPostcode2 = (select query for returning the second nearest postcode), nearestPostcode3 = (select query for returning the third nearest postcode) where postcode = 'KY6 1DA'; However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code: update table1 set (nearestPostcode1, nearestPostcode2, nearestPostcode3) = (select query to return the 3 nearest postcodes) where postcode = 'KY6 1DA'; The 'select query' in the above looks like this: select postcode from postcodeTable order by <equation to calculate distance> ASC limit 3
Posted: May 12 at 22:31
View 4 Replies!
View Related
Multiple Update By Multiple Users
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.
Posted: July 23rd, 2005 06:56 AM
View 1 Replies!
View Related
Php Update Multiple Row?
how do i update multiple row with one query? table id ref_no name ------------------- 1 a 2 b 3 c 4 e 5 f ................... since i just created new ref_no column and its blank. and i expect the column id = ref_no i tried below but giving me same ids: $q = $db->query("SELECT id_user FROM user"); while($r = $q->fetch_array(MYSQLI_ASSOC)) : $db->query("UPDATE user SET user_no='".$r['id']."'"); endwhile;
Posted: Apr 18 at 14:23
View 5 Replies!
View Related
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
Posted: November 18th, 2004, 05:24 AM
View 3 Replies!
View Related
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?
Posted: June 17th, 2005, 02:36 PM
View 14 Replies!
View Related
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?
Posted: February 7th, 2005, 03:53 PM
View 3 Replies!
View Related
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
Posted: October 20th, 2004, 03:49 PM
View 3 Replies!
View Related
Update Multiple Rows ..
i wnat to update multiple rows at the same time.. i have tried this. but i know is loking for ID=1=2 that will never find... but if i use OR it will update only the 1 one.. PHP Code: UPDATE `externas` SET `impresiones` = impresiones + 1 WHERE `ID` =1 AND `ID` =2;
Posted: November 25th, 2008, 11:42 AM
View 2 Replies!
View Related
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).
Posted: August 09, 2006 12:21PM
View 2 Replies!
View Related
Update With Multiple Instructions
I have about 1000 updates required on a table. The syntax i know is for example Update t1 set b_id=9264 where b_id=4259; and then the next one would be Update t1 set b_id=9358 where b_id=2253; etc etc Is there any way i can run an update with multiple instructions?
Posted: February 20, 2007 08:03AM
View 1 Replies!
View Related
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.
Posted: August 18, 2005 04:47AM
View 1 Replies!
View Related
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.
Posted: March 21, 2006 07:01PM
View 1 Replies!
View Related
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.
Posted: 03-11-2003, 08:19 AM
View 1 Replies!
View Related
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?
Posted: Oct 22, 2006, 02:45
View 1 Replies!
View Related
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?
Posted: Mar 24, 2007, 15:16
View 3 Replies!
View Related
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,
Posted: Jun 13, 2007, 15:30
View 5 Replies!
View Related
Update Multiple Rows From Php?
I'm trying to do: Update an arbitrary number of rows, changing several fields to the SAME value. $var = "1 3 5 7 9"; $query = "UPDATE tablename SET seen_before = 1 WHERE id = SET ($var);" What is the exact syntax on the Where clause when I lookin in a set, and how do I pass in the Set items through a php variable.
Posted: Jul 29 09 at 16:00
View 1 Replies!
View Related
Db Multiple Table Update?
I have 2 nearly identical tables (only different is one has an application wide primary key while the other one is only a temporary table to update from. I want to update everything in the "main" table from the temporary table, and I found the same query over and over to do this, however it never actually updates the main table. Code: mysql> UPDATE LA,LA_update -> SET -> LA.rec_owner = LA_update.rec_owner, -> LA.rec_address0 = LA_update.rec_address0, -> LA.rec_address1 = LA_update.rec_address1, -> LA.rec_address2 = LA_update.rec_address2,.........
Posted: November 11th, 2009, 11:58 AM
View 1 Replies!
View Related
Update Set Where Select Multiple?
completing two file compares with multiple possible results per line. file1 - Deleting domain names. format - domain.tld, date size - ~400k lines (16MB) file2 - dictionary words format - word size - ~30k lines First I take both files and import them. My tables: ... drop table if exists dict; create table dict( #id int unsigned not null primary key auto_increment, word varchar(20) not null primary key, str_length tinyint not null ) engine = MEMORY;............ This usually will time-out long before completion. What I have tried to improve performance: update expiring e, dict d set e.primary_word = d.word, e.dict_words = concat_ws(':',e.dict_words,d.word), e.filtered = 1 where e.name like concat('%',d.word,'%') and d.str_length < e.name_len and e.filtered = 0
Posted: January 10, 2010 02:21PM
View 2 Replies!
View Related
Update With Multiple Tables?
I am running an update of object but the problem I am not figuring out is let's say color belongs to another table called elements. How would I express that UPDATE object SET topval=round((color/shape)*2000) WHERE size=$size
Posted: January 12th, 2011, 08:42 PM
View 1 Replies!
View Related
Update With Multiple WHERE Clauses
i've already read the many threads on this forum which try to address this topic and tried out some of the proposed solutions, can't get this to work even though it seems like it's a simple problem. in a nutshell, i'd like to update several row values of a single column in a table, using only 1 query. i'd like a query which, conceptually, does this: UPDATE `products` SET `sold`=5 WHERE `id`=4, SET `sold`=42 WHERE `id`=17, SET `sold`=3 WHERE `id`=333, SET `sold`=87 WHERE `id`=1040 the query statement will be constructed dynamically depending on the products which have been sold. obviously i can do this w/'n' separate db calls [in this case n=4], but that seems like it's not really all that efficient.
Posted: March 22, 2011 05:02PM
View 3 Replies!
View Related
|
TRACKER
