MYSQL :: Updating Multiple Rows With Same Fields (in One Query?)

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin).

But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:

Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean.

I have a mysql query out put the data as a form like this:

Item 1
<input type="hidden" name="id" value="1">
<input type="text" name="order" value="">

Item 2
<input type="hidden" name="id" value="3">
<input type="text" name="order" value="">

Item 3
<input type="hidden" name="id" value="4">
<input type="text" name="order" value="">
<input type="submit">

Order need to be updated. I could setup to do a loop with multiple queries.

Updating Multiple Rows
I am running the following UPDATE statement

UPDATE history
set t_own = t_user * 0.75
where user_cur = 7
and lg = 'Local'

t_own and t_user are defined as DECIMAL 12,2

650 rows should be updated, but i get

error 1136 Column count doesn't match value count at row 1.

Updating Multiple Rows.
I went to MySQL home page and found one comment that kind of tried to explain it but it was written in such a manner that I could make no sense of it at all.
I am trying to find a way to update a row with 12 entries in a table that has 12 teams (teamid) and 12 ranks (r1,r2,r3...r12) the data to update the teamid comes from a form and they would all be updated (unless everything remains the same) with the use of the said form.
I am not certain how to make this work (call the table 'team')

PHP Code:

UPDATE team SET teamid='team1', teamid='team2'...teamid='team12' WHERE?

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

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'";

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

How Do I Update Multiple Rows In One Query?
How do I update multiple rows in 1 query? I try not to do a loop of update queries.

Updating Fields
I'm trying to update values in one column. Some values contain numbers and some are blank. I need to update both but I'd like to do it one query instead of two but I can't figure out how. Example:

tableA has 2 columns, item and itemNum. Lets say there's 10 records and 5 values for itemNum are blank, the rest have a number. I need to update itemNum's all blank values to one number and update the initial values as well, but in one query.

In other words, I know how to make the query's one by one but I'd like them both in one query.

Example single query's:
UPDATE tableA SET itemNum = '10' + item WHERE item = 'cd';
UPDATE tableA SET itemNum = '20' WHERE itemNum = ' ';

btw, the blank values are not null, they just have a blank string.

Updating Fields' Value
UPDATE `blablabla`.`blabla_db` SET `work_y` = '1' WHERE `blabla_db`.`id` =1 LIMIT 1 ;

Ok, the one above only updates one field's value, it only updates id 1, which sets work_y to 1. What i want to know is how to update all ids at the same time, which sets work_y to 1. Is there a way to do that?

Updating SET-fields
I've got a table with a set field. This is the create statement for the table:

CREATE TABLE `test` (
`field_a` int(11) unsigned NOT NULL default '0',
`field_b` set('a','b','c') character set latin1 collate utf8_unicode_ci NOT NULL default '0') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now I want to store integer values. That means I type in the mask value, not the set-values itsself. This is the query: INSERT INTO table (field_b, field_b) VALUES (5,6)

Everthing works fine on my MySQL 4.1.13-nt server. But it doesn't work with MySQL 5.0.21 (running on ubuntu).

Updating Fields
I'd like to do something like this:

update table1 set field1=field0 from table2;

so that the update operation is not based on any static values and does an update instead of an insert.
Could someone tell me if I can do something like this on command line, or do I need to write some code to traverse over every value in field0?

Updating Rows
I have a table say like this:
t1

v1 AUTO_INCREMENT
v2 char(40)
v3 int (its empty, need this in here!)
and say I have a CSV file with the values that I want to add, not in the same order as the values in the table, but the first column contains the index, v1.
1, "1234", 5
So is it possible to import like this? or would it be better to dump the whole table into a CSV file, add the data I need to and reload it in?On that subject, if you set a value of the auto_increment column when using LOAD DATA INFILE, will it set it providing that the value does not exist twice as requsted? or will it ignore it and auto_increment one up? because i need all the numbers to remain the same as they are linked into other tables from this index value!

Updating Rows
I have a table like this:

id name
-- -----
1 red
2 green
1 blue
3 yellow
4 black

I want to update the id's to 5 for names = red,green,black

How can i achieve this in one query?

Updating Rows
I am trying to update a table with content from a different table.

specifically, I am trying to add city and province info to rows which already have name and email info.

if I run this query

INSERT IGNORE INTO `jos_acajoom_subscribers` ( `city`, `province`)
(SELECT city,province FROM jos_fabrik_formdata_1 )

it adds new rows for contacts not already in the table, BUT it does not update existing rows.

Updating Rows While Fetching?
I'm trying to be able to do somthing here, my problem is figuring out a way to check through every row in a table, and write the timestamp date value that results in my set of if statements in a specific field of the row that I'm checking. Here's the code that I've written so far, and it's not working... Any suggestions?

<?php
$db->query("SELECT * FROM gtraining WHERE start_date<NOW() AND end_date>NOW()");
while($db->fetch()) {
$thisid = $db->row['id'];
if ($db->row['date1'] > date('YmHis')) {
$nextdate = $db->row['date1'];
} else if ($db->row['date2'] > date('YmHis')) {
$nextdate = $db->row['date2'];
} else if ($db->row['date3'] > date('YmHis')) {
$nextdate = $db->row['date3'];
} else if ($db->row['date4'] > date('YmHis')) {
$nextdate = $db->row['date4'];
}
$query = "UPDATE gtraining SET next_date='$nextdate' WHERE id=$thisid";
}
?>

Updating Rows Problems
im doing a program in visual basic 6 with mysql as database, in some part of my program i edit some fields but when im try to update an error shows, it says:

"Runtime error: -2147467259(80004005) Key column information wrong, Too many lines affected in update"
i dont know why, when filter i only have one row, i see it with the recordcount command, here its the code:

'Conn its a conection that i made
rsPartidasOC.Open "SELECT entregado, restante FROM mifpartidasoc" & _
"WHERE apunta = " & txtNOC.Text & " AND codigo = '" & strCodigo & "'",
Conn, adOpenDynamic, adLockOptimistic
ntmp = rsPartidasOC.RecordCount ' here recordcount its 1, only one row
rsPartidasOC![entregado] = ValLlegadoTemp
rsPartidasOC![Restante] = ValRestanteTemp
rsPartidasOC.Update 'the error comes here
rsPartidasOC.Close

**VALUES**
txtNOC.Text = "2"
strCodigo = "ART1"
table mifpartidasoc:
id apunta codigo llegado restante
19 1 ART1 100 1000
20 1 ART2 10 900
21 2 ART1 200 1500
22 2 ART2 150 20

Updating Exisiting Rows From A File
There's probably a very simple answer to this, but I can't find it anywhere....

I have a pricelist table with three fields
product_id
product_description
product_purchase_count
product_price

I have 150 rows (ie products) in my pricelist.
How can I update the pricing only (product_price) when the I have a text file (CSV)
with product_id and product_price colums?

I have used LOAD DATA INFILE to populate the table, but now I'd liek to do an update.

Updating Multiple Tables
My webserver is running an older version of MySQL (3.23.56 ). I am trying to update a table based on criteria from two tables, and I can't seem to get the syntax quite right. Here is what I tried:

UPDATE (member, unit)
SET member.status_id = 3 WHERE member.unit_id = unit.unit_id AND unit.etime > 0

Does anyone know an older syntax to do this?

Updating Table After Doing Multiple INSERT
I'm currently trying to do the following :

INSERT INTO sometable (userID,otherstuff) select ....blah blah ORDER BY RAND() LIMIT 10;

So I do that to get 10 random entries which works fine, however is there an easy way to then say...

For each of the 10 random userID's just inserted update another-field in another table with matching userID to the ones just inserted?

Updating Multiple Username In One User Field
how to update one field with 5 name of users and looping it
till the end of records.if i do this

UPDATE CALL_BARR_LIST_READY
SET [USER]=''
WHERE [USER] ='KUREIL'

it will filled all the records with KUREIL as a USER.
but now i have 5 users and > 1000 records to update
where each user get the same total no of records.

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

Combine Fields From Many Rows
I have a table (see below) and I want to combine all the like named items with thier respective fruit and output that to a new table (1 name many fruit). I seem to be having problems getting the syntax down:

---------------------------------------------------------------------
ID | Name | Fruit
---------------------------------------------------------------------
1 | A | Mango
2 | B | Apple
3 | A | Pineapple
4 | B | Banana
5 | C | Pear
--------------------------------------------------------------------

I created another table called "myFruit" that have 2 field : Name, TheFruitILike

I want to update this table with the info from table "Fruit" . It should look like this:
-----------------------------------------------------------------
Name | TheFruitILike
------------------------------------------------------------------
A | Mango, Pinapple
B | Apple, Banana
C | Pear
----------------------------------------------------------------

What kind of command should I use?

Multiple Fields
i have a query regarding mysql like clause.can we use
select name like 'john%' or 'bill%' from nametable ;
Is the above syntax correct or is it wrong.If wrong please give me the correct syntax.
what i am trying to do is that i am trying to select either john or bill with sum last name in all the rows.

Swap Two Fields Bertween Two Rows
I have a table in which a field (ordine) is used for the orderer output of data.

This is not an index, since data are subdivided in different categories and therefore the value is not unique for the whole table.

Sometimes I need to change the display order and I was used to use a query like this one:

UPDATE globalmoneta as mon1,globalmoneta as mon2 SET mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

probably not very efficient, but acceptable since this operation is not often performed.

Unfortunately, after a server upgrade this query is no more working. I suspect this is due to a different mysql version but I can not check it.

Any idea on how to obtain the same task with a different query?

Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat.
Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected.

I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of
$query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";

Show Rows With No Matching Fields
here is my query which probably needs some more advanced join statements:

select * from orders o, customers c, orders_styles os, styles s where o.customers_id = c.customers_id and os.orders_id = o.orders_id and os.styles_id = s.styles_id group by o.orders_id

the problem is that sometimes, because of user input, there will be no matching fields in the tables orders_styles or styles (no rows with matching orders_id exists in table orders_styles to table orders). I still want to show these rows however. Right now, as you can see it only displays rows that have matching rows in orders_styles and styles.

Search With Multiple Fields
I have a form with essentially 5 different selects, let's call them country, state, subject area, tag, and age range. What I want to do is find all of the users who are the closest match to the search query (once I know how to do this, I can modify the query to find their names, email addresses etc...)

In the database, users have a country_id (not name!), state_id in one table, and subject area id, tag id, and age range id in another table (all in the same column as multiple rows for the same user).

I want to do a database search and match all of the users who meet at least 1 of the criteria, and sort them in order of closest match (all 5 criterion) to least match. Assuming I consider the order of preference to be subject area, tag, age range, country, and then state, how do I do this?

To make the problem even slightly more difficult, the form doesn't return the actual country name, it returns the row the country shows up in the country table (same for the other fields) so I'm currently doing a look up for each field to figure out which country/state/etc... I'm actually dealing with.

I don't see how I can do a FULLTEXT search for this information because it is all in different columns.

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.

Count Of Multiple Fields
I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

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 Distinct Fields
Okay SQL gurus, I have a table laid out as so:
RowID | UserID | UserType | pID

Now, how can I write a SQL query that would select every distinct UserID and pID combination.

So if the data in the table were:

1 jjdoe internal 19
2 jjdoe internal 6
3 jjdoe internal 6

It would only return rows 1 and 2.

Ordering Multiple Fields
I'm relatively new to mySQL so I've been looking around the internet for help on how to order based on a user-defined pattern and haven't seen exactly what I'm looking for. If someone could help with this example I created to illustrate what I'm trying to do in general, I'd greatly appreciate it.Let's say I have a table in my database of Army officers with the following fields: rank, lastName, firstName.

I'm looking for the right query so I can display in an HTML table every officer in the mySQL table and order the rows first by rank, then by lastName (if ranks are the same), then by firstName (if ranks and last names are the same).

Displaying Multiple Fields
I am sure amongst this community that this question is rather straight forward.
I have a database where a table is added that contains the user login, password and other user information.
What I wish to do now is for each user I want to be able to add a product description, old price and new price.
So when this particular user logs in, they will see a list of products on one column and then the old price in another column and new price in the third column.
The prices obviously have to be matched to the same product.
Eg:
productA new_price1 old_price2
productB new_priceX old_priceY

How would one do this?

Comparing Multiple ID Fields
I have an advert table which contains three category ID fields (catid, catid2, catid3). These catid fields match up with the catid field in the category table. I want to run a query that returns any row that contains any mention of a specific category.

SELECT * FROM advert, category
WHERE advert.catid = category.catid
AND advert.catid = '3'

The above query works fine for the first advert.catid field, but once I introduce...:

OR advert.catid2 = '3'

... the results start getting strange. The same row is returned 9 times.

GROUP BY Multiple Fields
Can somebody show me how to properly use GROUP BY if I'm using multiple fields.
For instance:

I have a table that has a month field and a year field. I want to group all entries by month and year. How do I prevent grouping a record from January 2007 with a record from January 2008?

Indexes With Multiple Fields
I have seen some tables that have indexes which contain multiple table fields. What is the purpose of having an index with multiple fields instead of a seperate index for each?

Duplicates On Multiple Fields
The table has fields:
home_id, owner, street_number, direction_one, street, street_type, city

I have a problem where the same address information was entered in but with different owners. How do I find all the duplicates? I tried this query:
SELECT home_id, street_number, street, city
FROM home_data
GROUP BY street
HAVING ( COUNT(street) > 1 )

but that only works for duplicates on street, at the very least it should check for dups on street_number and street and city. When I ran the above query it took forever. My table has over a million records in it. Basically my computer froze up but kept running and I kept getting virtual memory errors.

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

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.

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.

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

How Change Multiple Mysql Fields?
I have a table with 2,733 records.

I need to change a field (published) on all records to show ....

Multiple Search Fields And Subqueries?
I'm trying to construct a query in PHP that will be able to handle multiple search fields. The data is in multiple tables.

COMPANY
company_id
name
address

SERVICE TYPE
company_id
service

OFFICE LOCATIONS
company_id
city_name

My question is how do I construct a query that will allow there to be these three search fields (company name, service type, location).

I know that subqueries are probably needed, but I'm a newbie and still not quite clear how to get these to work properly.

The queries I've been able to construct return zero results unless all fields are filled in, or they return multiple listings of the company name since the company may have more than one office location. I don't want the user to be required to fill in all fields to get meaningful results.