MYSQL :: SSH: Select Database And Update Rows

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

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,

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.

Update One Field In All Rows
I have a mysql database which contains 24,000 rows. I added a field called "RECALL", which contain null values. What I'm trying to do is fill that field in all 24,000 rows with the value "N".

Update Yields Zero Rows
I have a PHP query that is suppsed to update a table. When I run it mysql_affected_rows returns a zero. Unfortunately there are no errors I can find to see why. If the query "update" fails like this how do I find out the reason?

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:

Update All Rows Conditionally
Having a bit of trouble getting this syntax right.

It needs to go through each and every row, read a field and if that field = a certain text, update another field in that same row.

if(locate('text to locate' , `dbName`.`tblName`.`fld1`) ,

update `dbName`.`tblName` set `dbName`.`tblName`.`fld2` = 'new text') .

Rows Changed In Update SQL
Is there a way to find out how many rows got updated when i execute a update stmt.

I would like to get the updated rows count in SQL.

Let me know and Thanks in advance.

update stmt where clause;

now how can i get the count of rows that got updated.

Update All Rows In A Column
I'm a complete newbie but I'm trying to do something that I would think would be easy. In my table I have a column LICLEVEL with values of Club, Expert and Elite. I need to tack on "USA Cycling - " in front of that value, so that if the current data is "Elite" after the update it will say "USA Cycling - Elite". With 800 records to update, I'm hoping there's a fairly easy way to achieve this with SQL (or PHP).

MySQL Update Speed With 10K+ Of Rows
I Have table called `users` which have 10K+ rows. Each user have a fields called `gold` and `level`. Now each user should get "pow(1.25, level)*100" gold per/hour and it should divide so that users will get 1 gold per time unit.

example: user level is 7 and he should get 1.25^7 * 100 gold = 476 gold per hour (1 gold every 7.5 seconds). Now I have a problem. I can't update all users at once every second because it would create too much load for server (mysql load was 100% if I did it). Even if I would do this update in every 7 seconds, it will be too much load for the server.

maybe we can determine user gold amount so: user gold = user gold + (seconds_after_last_update/3600); and do update every prime hour? but what happens the when user wants to spend this gold?

My mysql-updateing daemon is running in infinite loop doing 1 action every second.

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

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:

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.

How To Update 860 Rows Date +1update
I am having trouble updating a field incrementally. I can update with any number, but how do you make each row 1 more than previous??

Update Script, But Check If The Value Is The Same On Other Rows
I have a nice user script. In a particular section there is the ability to edit the users on the databse.

What I am concerned about is that people may edit the username or email and find its the same username or email that another user has, this can screw up the login system especially two usernames being the same.

So is it possible to make it so that if the email/username is the same in another row to give an error, Please note that the input fields for their email and username are already filled in so if they dont come to edit it them they will still output there original username/address.

MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key
constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:

Update Multiple Rows Within One Table
I have a Dynamically created formfield. Tgis field shows the attached devices off a selected device (for instance you have a pc and in the formfields it shows two attached monitors). The monitors however are in the same database table. (with an other device_id ofcourse). The page with the pc info is shown by using the device_id in the URL.

What I want to do is update some fields in this table for the pc but also for the attached devices....

When I do it like this:

UPDATE dev SET .... etc.
WHERE device_id IN ('#FORM.koppel_2#')

It selects both device id's like this: WHERE dev_volgnr IN ('602,603')
This doesn't work! However in this example the table should be updated for the devices with device_id 602 and 603.

Is this possible? How would I do that?

Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have.

Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field?

For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?

Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin.

THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN:

$sql = "update table set color='red' where id='1';";
$sql_do = mysql_query($sql);

THIS QUERY WORKS ONLY IN PHPMYADMIN:

$sql = "update table set color='red' where id='1'; update table set color='green' where id='2';";
$sql_do = mysql_query($sql);

I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.

The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people
The table is quite large (300,000 rows)
I need to write code that update many of the rows in the table

Here is what I need to do
Two of the fields that I store about each person are: location and country
The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on

I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly

My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements

I cannot use LOAD DATA as this is product database

Is there a way to update multiple rows (each with a different value and condition in a single query?

How To Update/Delete Bunch Of Rows Quickly?
I have a textarea that allow user to edit a bunch of rows of data quickly. It either add/delete/update rows. How do I do it quickly? The db table has rows of names.

e.g.
the db table has:
car |Susan
dog |John
monkey |Chris

John enters:
plane
boat
house

After processing, the db table should say
car |Susan
plane |John
boat |John
house |John
monkey |Chris

How do I update the db table quickly? If I use update query, it will update John's rows but there is no rows to update? There are only rows to add and delete at this time. I don't want to delete all records of John every time John enters the data, because a user can potentially have a lot of rows in the table.

Select Only The First X Rows
Is i posible to select only the first X rows? Or row X to X?

ID want help me, it will be diffrent from one day to another...

Now it might be ID 1 to 3 but later it is ID 6 to 3 on one set and 0 to 3 on another

Select Rows
I had 2 tables here, tbl1 and tbl2. Now we got 6 items in tbl1, and tbl2 store the color for each item (maximum 3 colors for each item).

tbl1
----
id(PK)   name
1        Jack
2        Amy
3        Bill
4        Ken
5        Rye
6        Icy

tbl2
-----
id(PK)  t1_id(FK)   color
1        1          green
2        1          red
3        1          blue
4        2          red
5        2          yellow
6        2          blue
7        3          purple
8        3          white
9        4          blue
10       5          black
11       6          red

What I need is a query to select item from tbl1 WHERE none of its color is 'green' in tbl2. Suppose item 'Jack' is not qualified but the query below will include 'Jack' in the result.

SELECT tbl1.id, name, color FROM `tbl1` left join tbl2 on tbl1.id = tbl2.t1_id where color != 'green'

I got no idea how to write a query to exclude 'Jack' since one of its color is green.

SELECT With 1,000,000 ROWS
I have aproximately 1,000,000 rows and I would like to do some query. The
first one is to get the number of row so I do :

mysql> SELECT COUNT(*) FROM `Log`;
+----------+
| COUNT(*) |
+----------+
| 969129 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
+--------+
| Nb |
+--------+
| 969129 |
+--------+
1 row in set (1 min 20.99 sec)

But like you can see it, it take a long with the WHERE clause. I use Pentium
III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
computer but I'm not shure (I need more RAM ?).

SELECT With 1,000,000 ROWS
I have aproximately 1,000,000 rows.The
first one is to get the number of row so I do :

mysql> SELECT COUNT(*) FROM `Log`;
+----------+
| COUNT(*) |
+----------+
| 969129 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
+--------+
| Nb |
+--------+
| 969129 |
+--------+
1 row in set (1 min 20.99 sec)

But like you can see it, it take a long with the WHERE clause. I use Pentium
III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
computer but I'm not shure (I need more RAM ?).

Select Rows With Max Value
Given a table like this:

CREATE TABLE IF NOT EXISTS `people` (
`name` varchar(10) NOT NULL,
`age` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`name`, `age`) VALUES
('bob', 13),
('john', 25),
('mike', 25),
('steve', 8),
('sue', 13);

How would I select only the rows with the highest 'age' value?
In other words, because "john" and "mike" are the oldest, I want only their rows.

Select Rows
I am trying to create a table with certain select rows from another table. One column in the original table is called ID. I want only those rows with certain ID values (say 56 , 78, 89, 90) etc to be in the new table.

Select Rows
i want to select records between 11 dec 2004 to 25 dec 2004 plz tell me qry.

Select All Rows
I'm trying to figure out how to select all rows created during the current month. So, for instance, now I would want it to return ALL rows created during May. Next month, it should show every row created in June 2005. In my database, I have the field "date," where a date is stored in mm/dd/yy syntax, and I also have a "timestamp" field, which is created using PHP's time() function.

Select All Rows
I need to look in table 1 and pick all those records not in table in 2.
I know that table 2 has 51000 records and table 1 has 65000. I want to see the 14000 in table 1 that doesn't have a match in table 2

Num Of Rows In SELECT
I am just wondering if it is possible in one query to select some rows using WHERE and LIMIT clausule, and get the number of rows I would get when i dont use LIMIT.

if I have
SELECT * FROM table WHERE column1='something' LIMIT 10, 15;
i know it is 15 rows selected. But i need to know how many rows are in:
SELECT * FROM table WHERE column1='something';

I know, there is a way.. just to do both SELECTs.. and retrieve data from the first one, and num of rows from the another one..
But I just dont want to make two SELECTs if there is maybe some solution how can i do it in one query.it for page with forum where i want to show users how many threads are in database, but at one page i need to show just 15 threads...

Select The First Letter Of Each Rows In A Db
I want to be able to select only the first letter of each row in my table.
I want to select only DISTINCT row so i would have as a result, an array of letters. For example, if i have 25 rows starting with the letter 'A', well 'A' would come out only once.

Select Every 3 Rows From A Table.
I have a bit of a problem, Iam doing a match results page with data from mysql.

My table Name: JuniorMatchResults

Data Colums: id, date, venue, position (1,2 or 3), weight.

What i wanto to do is output this information in my website such as you would have the 1st 3 positions outputted in a table, the the next set of 3 in a table and so on.

Is That Possible To Select Range Of Rows?
I use simple query right now to select rows from database:

SELECT * FROM cities WHERE country = '$country' ORDER BY city

But what if I need to select not all rows (from 1st to 800th, for example), but only rows from 70th to 140th? Is that possible to select this range with MySQL query?

SELECT Results After X Rows
I am trying to run a league creation script using php and mysql. I was thinking that i could do it by getting every user into one column and then pair them off by starting from the second result found and place that with the first user in the first fixture row. For the next fixtures i would place the 3rd result found in a row with the initial users.

This way i make sure they dont play each other twice and each fixture created in unique.

Is their a mysql function to do a select query but dont have it start outputting the results until it reaches a certain row from the results? I will also need it to go back to the first result found once it reaches the end of the results.

Select Rows Not In Second Table
I want to select all records in a "User" table that are not in the "Groups" Table.

Select
users.USERID,
users.NAME
From
users
Outer Join groupmembers ON users.USERID = groupmembers.empid
Where
groupmembers.groupid <> '9'

In therory this would return me all users not in group 9, but I am getting an error in my SQL... I dont really understand Left - Right - and Outer joins but I have tried all three... I could do this in 3 queries - Create a Temp Table - Delete Records w/ 9 in group ID ... Select remaing records.... But there must be a better way...

How To Select Rows Which Contains Certain String
How can I select rows which contains certain strings. Using "Like" clause we can do but its not giving exact results.

Select 5 Rows Per Column Value
ID | NAME | EMAIL | CITY |

How could i select 5 rows for each city value?
In other words, if there are rows containing St. Luis, NY, LA - I want to select 5 users from St L, 5 from NY and 5 from LA.

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

Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this:

[news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid]

One entry in 'news' can be attached to many different categories via 'newscategory'.

What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,

Select Duplicate Rows
In a table, i have records about phone number and stateID, sometimes there are rows that have the same phone number with different stateID (assume stateID has value from 1 to 50, or 100), I want to find out duplicate phone numbers with stateID value of 100 (those same phone numbers also contain stateID of value between 1 and 50). Can someone please tell me what would be the query for selecting this?

SELECT And UPDATE
I was wondering if it is possible to combine a select and update command.

Basically selecting a single row and returning it; while updating a value in that same row?

Update & Sub-select
I'm getting the error msg "you can't specify the target table 'orders" in the from part of an update" when executing the following query

update orders set status='READY'
where status='PENDINIG' and parent_id in (
select p_orders.id from orders p_orders where p_orders.status='OK')

Does this mean that I can't do a sub-select on the same table as I'm updating in MySQL (I'm using ver 5.x) ?

Would anyone know a way around this - I'd basically like to update an order once its parent order is in the state 'OK'.

Ref:

create table orders (
id bigint(18) unsigned not null auto_increment,
status varchar(32) not null,
parent_id bigint(18) unsigned,

constraint fk_order_parent foreign key (parent_id) references orders(id),
primary key (id)
);

insert into orders values (null, "SENT", null);
insert into orders values (null, "OK", null);
insert into orders values (null, "PENDING", 2);

UPDATE Using SELECT
Coming from an Oracle background I am used to doing this:

UPDATE accomodation a
SET a.country = (SELECT c.country
FROM country c
WHERE c.country_id = a.country_id);

I could not get this to work so looked up the syntax for this:

update accomodation a
join country c on a.country_id=c.country_id
set a.country=c.country;

Which doesn't work either !!! Basically I am trying to do an update using a select from another table rather than a static value but cannot get it to work. Is this possible in MySQL ?

Update And Select...
Can I do an update and select in the same query?

I want to show a record and also update the "times it was viewed"...is that how I'd do it?

Update And Select
an update that doesn't affect any records. As a test, I switched it to a Select statement. Both statements have the same where clause. The select statement returns the number of rows I expected to update. Here are both queries. Any help greatly appreciated.

UPDATE program_event set measure=239, measure_date='2007-01-25' WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

select * from program_event WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

Update And Select
Code:

"UPDATE `links` SET
`in`=`in`+1
WHERE `url` LIKE '%google.com%' LIMIT 1"

alright, so i want to a) update the link to in+1, but also b) return a variable called 'user' containing the 'id' of the link, all in one statement because it is faster and gives me the same link's id that was updated. is it possible?

Select + Update
I managed to do something like this:

$query = sprintf("SELECT CONCAT('UPDATE articles SET visits=(visits+1) WHERE title='".$name_article."'')
FROM articles a
LEFT JOIN autors b ON a.autor = b.name
WHERE a.title='".$name_article."'");

$result = mysql_query($query);

Without the part underlined, it works pretty good. What I want is to be able to select all columns from the article table and autors table, based on the article name, and, at the same time, update the visits field in the article table. I really dont know if its even possible to do that in one single query.