MYSQL :: Updating Multiple Rows In One Query

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

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.

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 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 .

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.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address
AND localhost@my_field_db FROM my_field_tablet_pc to affect
a custom sycnchronization that SQLyog will not allow.

Both remotehost and localhost are built on WAMP stacks.

Multiple Rows
I've got a whole bunch of rows to create, each with a unique key.
Each row gets the same value ("New") set in the "Age" column.
I've seen the INSERT INTO table (rows,) VALUES (val for row1), (val for row2),
etc.But what if each row gets the same exact value?

Multiple Rows
I was wondering if it is possible to get all of the inserted id's of an auto increment column when doing multiple inserts at 1 time. For example:

INSERT INTO people (fname, lname) VALUES ('john', 'smith'), ('eric', 'robinson'), ('mark', 'appley');

is it possible to retrieve all of the insert ids of those inserts instead of having to loop through each individual insert and retrieve each individual row id?

Multiple Rows
I create a table with 7 columns
id Mdate col1 col2 col3 col4 col5

as above shown
id is small int, mdate is date and col1 to 5 are tinyint
and i want to insert the values . id is auto increment and i want mdate a day for one row. and other column set to zero
what i want is to enter in single query.

Get Multiple Rows Using Subquery?
I have a query similar to the following, however i'd like to get another row from the subquerys - currency.

SELECT *,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 19 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_1,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 13 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_2,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 12 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_3

FROM (

SELECT *
FROM
[..snip..]
GROUP BY lodging_master.lod_id
ORDER BY RAND(&#55614;&#57159;-12-17')

) as foo
HAVING (price_from_1 > 0) && (price_from_2 > 0) && (price_from_3 > 0)
LIMIT 0 , 30

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,

Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null.

So here is my problem:

I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?

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.

Collapsing Multiple Rows Into One.
I'm setting up a database of links, where each one can be in multiple categories. I could make each category id a column in the links table, but I'd rather store it in a separate table so I don't have a bunch of NULLs cluttering up the main table and also to allow unlimited categories. That table has two columns, linkid and categoryid. The problem is that I'd like to retrieve it as one row somehow, something like this: "linkid category1 category2 etc..."

Selecting From Multiple Rows??
i have made a voting script... every time someone votes it goes into a table called 'voting'... since people can vote on multiple things in the site there are multiple instances of each user in the 'voting' database...

if each entry has a 'userID' a 'ratingGiven' and a 'objectBeingVotedOn' field
how would i go about grabbing say the "ratingGiven" field from all of one particular user's entries .

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:

Insert Multiple Rows
I want to insert multiples rows and use sintax
"INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);".

But, I get the following error: "Column count doesn't match value count at row 1".
The number of fields is the same, only that insert multiple rows.

How To Insert The Same Value In Multiple Rows?
i just added a new column to one of my tables. it is an int column, and is currently
assigned 'NULL' in all instances. How can i insert, for instance, the value 0 into all rows?

Inserting Multiple Rows Of Same Value
I have an auto-incremented column and I need to be able to insert one value hundreds of times. Does anyone know an efficient means of doing so?

How To Evaluate Multiple Rows As One Using IF( )
Each record for a PhysicalSource has a Status number. I need to do a SELECT statement to find out if there is a Status = 2 in any of the records.

If Status = 2 then I need to return all rows for this PhysicalSource else if Status != 2 then I need to exit the query (donâ€™t return anything).

The statement is as follows:

SELECT SourceID, OriginalTime, State FROM sources WHERE SourceID = ? AND PhysicalSource = ? AND IF(Status = 2, 0, 1) Order by SourceID ASC

As I found out the â€œIF(Status = 2, 0, 1)â€� evaluates every row and returns all others with Status !=2.

The options are limited, I inherited a GUI (canâ€™t modify it) that must be use and only accepts one query at a time using MySQL version 4.1.11.

Could someone help with a way to have this evaluate multiple rows, as one, and if in any of them Status = 2 then exit the query?

Matching Multiple Rows
I've got the following tables with the following columns:

property: id, address, number_of_bedrooms
attribute: id, title (e.g. washing machine, central heating, furnished)
property_attribute: property, attribute (composite primary key on both id columns)

what i'm trying to do is select property ids that have all the attributes i'm looking for, for example, all those properties that have a washing machine and are furnished.

I've been trying things like the following which aren't working, probably because the attribute column won't be equal to two different values at once.

SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE a.title='washing machine' AND a.title='furnished'

this is returning an empty set because, i think, a.title can't have two values at once. what i want to say in pseudo-sql is:

SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE
(pa.property=X AND a.title='washing machine') AND (pa.property=X AND a.title='furnished')

so the only results that will be returned are where a property has both attributes.

i can't think of how to do this since there will be varying numbers of attributes i need to search on. perhaps i need to use a variable for pa.property - i tried that, setting it to SELECT DISTINCT property FROM property_attribute, but mysql didn't like it because this query returned multiple rows. i may need some kind of set variable or an array.

Select Multiple Rows With The Same Id
So I have this table:

table_name

id_one ---- id_two
1 ---------- 1
1 ---------- 2
1 ---------- 3
2 ---------- 1
2 ---------- 3

I want to select all of the id_one rows where the id_two is '1' and '2'. The desired output is:

id_one
1
1

Later I might use SELECT DISTINCT to produce the following:

id_one
1

So far I have:

SELECT id_one FROM table_name WHERE id_two IN ('1','2')

But this selects all the id_one rows where id_two is '1' OR '2'. I want the rows where it is '1' AND '2'.

Delete Multiple Rows At Once
My question is, what code would i use to delete multiple rows of data at the same time.
if i had:
username password userid
john fubar 13402
mike yippy 13679
and i wanted to erase both users in one query, what would it be?

Subtracting From Multiple Rows.
I'm wondering if it's possible to subtract a single value from multiple rows. For example, say I have two rows containing pieces of an account balance, which add up to a total balance. We'll say the two rows hold balances of $10 and $20. Now I want to subtract $25 from them (meaning subtract $20 from one and $5 from the other). Is there a way to write this in one query? If I use the SUM() command can I subtract from the total instead of each unique row?

Insert Multiple Rows At Once
I was faking it a couple of years ago when I made my first database for my photography website. Now I'm updating it and I'm inserting One item at a time manually thru PHP. It's slow and tedious and I've got 467 entries to do.... I'm sure there's an easy way of doing it all at once. I'm just too much of a retard and I'm too tired to try to learn some MySQL tonight. Could someone please help me?
Here's the query that I'm ending up with. (I started at Number 100, I'm increasing the Number by 100 each time and I need to go to Number 46700.)

SQL query:
INSERT INTO `Headshots` ( `Number` , `Category` , `Name` )
VALUES (
'2800', 'Head', ''
);

WHERE Accross Multiple Rows
Suppose you have a table:
Code:

attribute_id file_id attribute_value
1 81 11076
2 81 BONAP
3 81 2
4 81 2004-09-09T00:00:00
5 81 JIM01
6 81 Bon App
7 81 Margaret Peacock
1 86 11072
2 86 ERNSH
3 86 2
4 86 2004-09-01T00:00:00
5 86 PO7859
6 86 Ernst Handel
7 86 Margaret Peacock

How do you find the file_id that has attribute_id = 1 and attribute_value = '11072' and attribute_id = 2 and attribute_value = 'ERNSH'?

Adding Multiple Rows Into A Table At Once
I have to the stage of being able to easily(?) add new rows to my tables using the command

insert into zone_vs_weight
(low_mass, high_mass, zoneA, zoneB)
(1, 250, 4.50, 6.35);
It seems to work well for adding single rows but can we use it to add multiple rows i.e.

insert into zone_vs_weight
(low_mass, high_mass, zoneA, zoneB)
(1, 250, 4.50, 6.35)
(low_mass, high_mass, zoneA, zoneB)
(251, 500, 6.50, 8.50)
.........;
Is there another command apart from the input file insertion or using PHP?
I have been using K. Yank's fine book 'PHP & MySQL' as a good primer but it doesn't seem to mention whether it is feasible.

Adding Up Data From Multiple Rows?
If I have numerical information stored in different rows what is the best method to find the sum of all the numerical information in the rows?

Insert Into Single Row Vs Multiple Rows
My question is: is it possible to add data separated by commas into an existing row that already contains some data?

Update Multiple Rows Outside Loop
I know it possible to update rows in loop. But how about outside the loop?
Normally i update in loop like this:

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)