Update Query On 14000 Records

Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:
1, 2, 3, 4, 5, 6, 7, 8, 9 . etc...

One of my utitities is meant to check the consistency of the table -
one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

---

How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:

1, 2, 3, 4, 5, 6, 7, 8, 9 .etc...

One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

---

Update 175,000 Records In 1 Hit
I have just under 175,000 records in my database and need to create an MD5 hash of a certain field in each row.

I created a page to run on localhost which had the following
PHP

<?php mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("wordlist") or die(mysql_error()); if (isset($id)) {     $id=$_GET['id']; } else {     $id=&#391;' } $GetThisWord_result = mysql_query("     SELECT *     FROM word_list     WHERE id=$id") or die(mysql_error()); $GetThisWord = mysql_query($GetThisWord_result); $GetThisWord = mysql_fetch_assoc($GetThisWord_result); $word=$GetThisWord['word']; if ($word=="") {     header("Location: ./end.php"); } else {     $query = "UPDATE word_list SET hash=MD5('$word') WHERE id='$id'";     mysql_query($query) or die(mysql_error());          $nextid=$id+1;          header("Location: ./index.php?id=$nextid"); } ?>


But the browser keeps coming up saying theres an error as the page will just keep repeating itself after it's processed 20 rows.

Can I just enter
PHP

UPDATE word_list SET hash=MD5('$word') WHERE id='$id'

or simiilar into MySQL Front and let it do it all itself?

---

Can't UPDATE Records
I'm trying to update all records from table 'Products' in my DataBase despite of having no erros the records don't update. Here's the code:

UPDATE `Produtos` SET `Estado` = '1' WHERE `Estado` = NULL ;

---

Update Records
Normally, an update on a record is done by:
1. retrieve & display the record details
2. html-form: change/update the record fields;
3. submit an update statement.
Now, thus will cause problems when another user is also updating the same record.
Now, what is common practice (or standard MySQL functionality?) to make sure that changes/updates in such a multi user php/mysql environment are dealt with properly? (e.g. record locking?), or comparing values of db-record with formfields to check whether an update has been executed during the time the up-date form was used? Loging time/date stamps?
Checkin/checkout?

---

Records Were Update
I want to know, when issuing an UPDATE statement inside a stored procedure, how many records were updated.

---

Can't Update Records Of Several Table's
i've migrated our access database to mysql, now i get the following error, when i try to update the contents of a record it states that someone else has edited the record in the meantime (though this is not the case), all records in the table give the same error while trying to edit. i guess it has something to do with locks but i do not understand what has gone wrong.

I'm using an access frontent connected with ODBC to the MySQL server wich containes the database.

---

Update Records In Table
i have installed a mysql server, and i have a problem with import record in database.
the origine is a file txt or csv, the destination database contain also other records, and i must update the records into database

---

Insert Or Update Records
What's the best way to achieve this?
To simplify the problem, lets say I have a table with two columns, 'id' and 'value'.
Now, I want to pass an object through remoting (amfphp, irrelevant really but anyway) to either update existing rows or insert new ones.
The object I'm passing has corresponding fields, and if there is a match on the 'id' field then update it, if not insert a new row.
So I guess I need to first pull out all of the id's and compare them to the ones in my object, then seperate them up to ones that need updating, and ones that need inserting.
I can't quite see what the best way of doing this is. Either in code e.g. PHP or is there a MySQL statement to do it the quick way?

---

Can't Update Records When Using Linked Tables In Access
I have a linked table to MySQL 5.0 through MS Access 2003 that utilizes a timestamp field in order to prevent the #Deleted signature happening everytime I create a new record. However, when I attempt to update a previous record I receive the error message: "Reserved error (-7776)" and am subsequently prevented from updating any records. Please note, there are no errors when creating new records, only when I attempt to update an existing one. However, if I remove the timestamp field, updates occur without error. Has anyone else encountered this issue? How might I rectify it?

---

Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read.

OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11).

With each shoe it does (or is meant to do) the following:
1. The PHP script that runs the query is looping through every size outside of the query
2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on
3. When it finds the size it is on, it then deducts the correct number of units from the stock table
4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before)

Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not.

Here is the query I have so far (with a little simple PHP around it doing the loop):


PHP

$shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11');
    $numshoesizes = count($shoesizes);
    
    for($i = 1; $i < $numshoesizes; $i++) {
        $stockupdate = "
        UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." =
            (SELECT
                CASE
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12
                THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13
                THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1
                THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2
                THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3
                THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4
                THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5
                THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6
                THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7
                THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8
                THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9
                THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10
                THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11
                THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price)
            FROM items_ordered WHERE items_ordered.StockUpdated = 0)
                
            WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name))
        , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails

        
        // update stock for size $i
        mysql_query($stockupdate);
    }

Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.

---

Update A Record Out Of 2 Duplicate Records Which Dont Have Primary Key In Mysql Table
how to update a record out of 2 duplicate records which dont have primary key in mysql table?

---

Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do...

Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night.

Here's a sample MySQL syntax for my selectquery:


SELECT * FROM table1
WHERE field1 = sometext
ORDER BY some_time_field ASC
LIMIT 0,6
So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?

---

Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f).

Now this works well, but what I want to do is say to my query is find all of these dates BUT

- As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....

---

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

---

Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menù), the fact is that it can display max the months wich correspond to the 107 record submited.

The query:

select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month

Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?

---

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

---

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table.  I not sure how to do that.  Here is the query I have now....

---

Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name.
Eg
Total records for author Eddings = 10

So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like:

Select
sum(Eddings) $total
From books

---

Query Total Records In 1 DB With 5 Tables:
In PHPmyadmin there's an option that shows you the amount of records in all tables, how can I did this via a query?

---

MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?

---

Query For All Records With Datetime In 2005?
I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

---

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

---

List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are.

Example:

1. Bill Jones - 55
2. Bob Smith - 56
3. Steve Gates - 25

Can I do this in mysql or should I add the code in PHP, and how do I do it?

---

Query To Display Records Over A Certain Number?
think my brain is now officially fried as i can not come up with this query....

i want to display records from my table where the record count is say over 5.

real simple...two columns a and b.  10 records in the table....

---

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

---

Counting Records In 2 Tables Using 1 Query
I have these 2 queries.

SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that

SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing

is there a way to put these into one query.....

SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .

so far nothing seems to be working .....

---

Flag Records Not To Be Reused After Query
I'm a MySQL novice, and I don't even know if there is a way to do this.

I have a simple table containing just 2 fields and about 15,000 entries...one contains an ID (which is just an incremental number) and the other a phrase.

I will be doing queries selecting 50 random entries from the table. Somehow, once those 50 are selected and returned, I would like to flag them so that they cannot be selected again UNTIL I exhaust the entire list. So, I'd like to get 50 random at a time, until all 15,000 are gone, and then start over.

If that isn't possible, I suppose I can just select the first 50 every time and delete the first 50 when I am done.

---

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

---

Query To Get Matched And Mismatched Records
this is my first time on the forums and i need help with my dbase. i need to match fullnames from Table1 with the fullnames in a QryCustomer. Table1 contains only the fullname(one field) whereas QryCustomer contains a list of fullname with their respective Account# and Customer#..i need to match the fullnames and a separate query to get the mismatched names.

---

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

---

How To Specific Number Of Records To Retreive In A Query?
My item file contains too many records to display. I want to limit the display to only 20 records at a time.

In the Select statement, how do I specify the the first 20 records, 2nd 20, etc.?

---

Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like:

SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY)

for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.

---

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

---

Query By Dates To Find History Records
what is the correct way to query on a date field in a table to pull a value that is older than 3 days?

Date field format is 2007-04-04

I only want records where the date is 2007-04-01 or earlier.

---

Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.  

Maybe a better explanation is given by example.  Think of having a table of employees, with one of the columns being their bosses email address.  Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.

---

Query To Result Out The Newly Inserted Records To Txt File
Can any one help me with a query to result out the newly inserted records from a MYSQL table to a text file on a hourly basis.

---

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

---

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

---

Update Query
I have a query question. I want to update a database that contains ips like 90.30.100.xxx. I only want to modify the third numer (100). Does anybody know how can i do this?

ex: i want to modify all ips from 90.30.100.xxx to 90.30.101.xxx. Notice if i had 90.30.100.100 i'd like to become 90.30.101.100

---

Update Query
I'm selling tickets and I need to be able to reduce the total number of tickets when one is sold. I have a table with the total number of tickets stored in a field and and a field with the quantity sold in a particular purchase. I think this is a fairly simple update query can anybody help get me started?

---

Update IN Query
Code:

UPDATE SET COUNT=COUNT+1 IN WHERE SELECT
sponsor,mstatus, count( username ) from customer Group by
sponsor DESC Having count( username ) = 2

---

Update Query
I am trying to do a custom sortup/sortdown function in php on a mySQL basis. Now I got that prob:

I got a table, that has a "sort"-field for each data like that:

data | sort
a | 1
b | 2
c | 3
d | 4
e | 5

The output of the data is ORDER BY sort ASC. Now I want the users allow to change the "ranking"/"sorting" themselves that way, that they click e.g. on sort entry d one up.

So the new table should look like that:

a | 1
b | 2
c | 4
d | 3
e | 5

Does anyone know how to do that in one or only a few mySQL queries? I mean I could read out the whole table and then do a php-loop giving new sort-numbers to each data and then update every single data, but I guess there should be an easier way in mySQL. My prob is that I suck big time at mySQL

---

UPDATE Query
Select TransType FROM TransTypeMapping WHERE vTransType=”CANCEL”, into array[0]

For each record from MainTable where TransactionCode = TransType.array[0],
Find record[1] where record[0].Category=
record[1].Category and record[0].Units1 = record[1].Units1

Action: Set fields in MainTable, record[0].vCancel = T; record[0].vMatchingTransID = record[1].vTransID
Set record[1].vCancel = T; record[1].vMatchingTransID = record[0].vTransID

---

Update Query
I am using phpMyAdmin to update table rows where the short_descr field (text) contains no characters.
I can execute this:
SELECT * FROM download WHERE short_descr='';
But this gives me an error:
UPDATE download SET delete=1 WHERE short_descr='';

---

Update Query
Code:

UPDATE users_tickets SET dateResponse=NOW() and Response='sgdgdsgdsfdsfds' WHERE ticketID='1'

i query the same database earlier in the code and it works fine. in phpmyadmin i tried editing it and it worked fine. this is the code phpmyadmin returns when you do it there
Code:

UPDATE `users_tickets` SET `Response` = 'mommy' WHERE `ticketID` =1 LIMIT 1 ;

---

Update Query
i need a query to change my customers_id number from an example
1000549 to 500600 and i made this query that doesn'work :

UPDATE customers SET customers_id = '50%' WHERE customers_id = '100%'

i need to change the first 3 digits from 100 to 50 and the other numbers stays the same.

---

Update Query
Here's my table set up:Code:

USERS user_id,firstname,lastname,tel,institution,email,username,password
BOOKS book_id, user_id*,created,author,subject,title,description
BOOKCHAPTERS chapter_id, book_id *
CHAPTERS chapter_id *, chapter_name, chapter_location

Im trying to update the chapter_name and chapter_location in the chapters table. The parameters I pass in seem to be ok, so I think it's the SQL update query below.
Have I done the update query correctly?Code:

update chapters c, bookchapters bc, books b set c.chapter_name = ?, c.chapter_location=? where b.book_id = ? and bc.book_id = b.book_id and c.chapter_id=? and c.chapter_id = bc.chapter_id;

---

Update Query
Can we update row when there is an empty field?
here is an example (attached):

How can I update the x?
Let's say x = 6

Like:
UPDATE table
SET [all_empty_fields]='x'
WHERE [field_before_that] ='5';

---