Update Self Subselect

Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this:


create table access_logs (
session_id varchar(32),
request_uri varchar(32)
);
Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri.

This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects:

select session_id
from access_logs
where session_id in (select session_id
from access_logs
where request_uri = "landing-page.php")
and session_id in (select session_id
from access_logs
where request_uri = "exit-page.php")
group by session_id;

---

Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid.

I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join?


SELECT *
FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE
FROM BID
GROUP BY ITEM_ID) as MAXDATES
WHERE
(ITEM_ID=IID) and (BID_DATE=MAXDATE);

---

Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this:

SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?

---

Subselect
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:

locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list. Code:

---

Subselect
Let's say i got this query:
select user.id, (select count(*) as posts_number from posts where posts.user_id = user.id), and some other fields, and a lot of joins here.Is it any way to *say* to mysql that the current user.id selected, is the one in the subselect ?
(the one from select "user.id" and the one in where ... = "user.id")

---

Subselect
Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.
SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";
Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

---

Subselect
I have been held up long enough on the query time to ask for help. Its basically a subselect that never returns.

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id IN ( SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC ) ORDER BY it.org_id ASC

If I break it up into 2 seperate

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id = 09238323 ORDER BY it.org_id ASC

SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC

They both return expected values.

---

Subselect / AS
SELECT
a.id
(SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename)
FROM users a

ORDER BY a.datestamp DESC

you can see my example, using with AS (example).
How can i extract values from subselects?

---

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

---

Subselect In 4.0.12-max With -- New Option
I was reading the manual and it said that the subselect is only
available in 4.1 or using the 4.0.12 with the mysqld =96new command line
to start it.

But it doesn=92t working!! So I downloaded the 4.1 alpha version with =
the
same problem. The error is:

ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near =85.

Any ideas? I need subselect working.

---

Delete Subselect
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:

delete from table_a where table_a.column_1 in ( select column_1 from table_b);

Assuming that column_1 is the same data type and size in both table_a and table_b.

---

Subselect Wierdness
I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

CODEselect AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

---

Subselect With NULL
why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

---

Get Last Records Details With A Subselect?
I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key). Now I want to get details from the last entry belongs to a ticketid.

kdn_message:
updated (date)
ticketid (int)
kdnr (int)
detail
state

select * from kdn_message t
where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc)

what is wrong i this statement?

---

Convert A Subselect To Inner Join
I was developing a php/postuke app for a client and I wrote two of my SQL queries with subselects.  I found out after I was done that they were pretty much stuck with MySQL 4.0.x for awhile, so I need to revamp my queries to avoid subselects.  The query uses three tables:

nuke_gwbt_guild_halls
nuke_gwbt_guild_halls_notes
nuke_gwbt_matches

I am getting all of the fields in the first table, matching the notes id from the second table to a notes id in the first table, and then counting some metrics from the third table to return as fields in the resulting recordset, used for ORDER BY sorting.  Here is the working subselect query: Code:

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

---

Subselect/left Join
I have a table like this

| ID | THING | NUMBER |
---------------------------------------------------------------
| 1 | white | 1 |
| 2 | white | 2 |
| 3 | green | 1 |
| 4 | green | 3 |
| 5 | brown | 1 |
| 6 | brown | 4 |

and I want to get just white back if I know two numbers are 1 and 2 or green back if I know the nubmers are 1 and 3.

Its mysql 4.1 so I am allowed subselects or left joins. I am drawing a blank!?

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

---

Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table:

CREATE TABLE b5_assignment_lookup (
as_id int(11) NOT NULL auto_increment,
as_blog int(11) NOT NULL default &#390;',
as_blogger int(11) NOT NULL default &#390;',
as_milestone enum('start','finish') NOT NULL default 'start',
as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`as_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ;
By running this query, I get the following resultset:


mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = &#3989;' AND as_timestamp <= &#55614;&#57158;-09-30'
+--------+----------+-------------+---------------+---------------------+
| as_id | as_blog | as_blogger | as_milestone | as_timestamp |
+--------+----------+-------------+---------------+---------------------+
| 87 | 89 | 41 | start | 2006-05-01 00:00:00 |
|208 | 89 | 41 | finish| 2006-09-02 11:55:27 |
|209 | 89 | 103 | start | 2006-09-02 11:55:27 |
+--------+----------+-------------+---------------+---------------------+
3 rows in set (0.00 sec)
What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41.

What I really need to grok is all bloggers who blogged all or a part of a range of dates.

For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02.

I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25.

The flags are the start/finish, obviously.

---

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

---

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

---

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 Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master.

What is the best method of re-synching the databases?

Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?

---

Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server:

Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database.

I can connnect and read the data and data definitions just fine.  I cannot insert, update, delete, create, or alter any table in this database.

Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy.  I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.

---

ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute.

Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table.

The format of the table is like this (with some example data to show you more clearly):

+-----------+-------------+---------+------+--------+------+
| ShopperID | Product Code | Product | Price | Amount | Units |
+-----------+-------------+---------+------+--------+------+
546733 1315 prod 1 64.00 64.00 0
546733 1316 prod 2 43.99 43.99 0

OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right.

Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do:

1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper
2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists.
3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing.

Does anyone have any ideas on how I can get round this?

Here is my PHP function which contains the query:
PHP

// this function for insert data to csv
function makeINSERTS($text, $table, $tablefields, $correctcsv){
    $insert = array(); //make array for hold data insert
    $i = 0;
    $success = true;
    
    while(list($key, $val) = each($text)){
        // Insert the data
        $insert[$i] = "INSERT INTO ".$table."  VALUES('";
        $insert[$i] .= implode("','", $val);
        $insert[$i] .= "') ON DUPLICATE KEY UPDATE ";
        foreach($tablefields as $k=>$field){
            $insert[$i] .= "`$field`='{$val[$k]}',";
        }
        $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1);
        // echo $insert[$i].'<p></p>'
        $result = mysql_query($insert[$i]);
        if(!$result) {
            echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />'
            $success = false;
            die('Query failed: ' . mysql_error().'<br />');
        }
        $i++;
    }
    if($success == true && $correctcsv == true){
        echo 'SUCCESS in uploading the CSV file to the database!<br />'
    }
    else {
    }
return $insert;
}

---

Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared.

Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly?

We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.

---

Need To Update PK Of Every Row
I want to update every row's primary key and need an SQL statement to do it.

There are 2 columns
PK
Placename

So I firstly want to order all the rows by placename in alphabetical order and then starting by updating the first row to 1, I then want to update every ID with an increment of one - all the way until the final row is updated.

---

Log Update In My.ini
If i put

log-update=update

in my ini it works fine,but as soon as try to specify a different location i.e.

log-update=/backups/update

i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work.

---

Update If?
I have a table that has an item field, a price field, a price min field, a price max field.

I want to be able to store the current price for an item as well as it's high and low. Is there a way to do an update on the row for the new price as well as compare the new price to the min field and max field and then update them if the new price is either higher or lower than the current values in the min and max field?

I know how to do it with multiple queries and comparing, but am looking for a clean way in one query.

---

Last Update
Is there a way in mysql to discern when the last date/time a field, row, or
table was been updated or inserted?

---

Can't Update
I'm current using MySQL and ASP.

But there seems to be a problem when i try to update a particular record. I get the following error message.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Query-based update failed because the row to update could not be found.

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM MainPageContents " & _
"WHERE BelongsToShop=" & Session("ShopID") & _
" AND ID=" & which

rs.Open sql, conn, adOpenKeyset, adLockOptimistic

rs.update
rs("some_fld") = "something"
rs.update

I tried setting cursor locations to aduseclient and aduseserver but to no avail. There's also nothing wrong with the query. Does anyone know if there's any kind of issue on updating MySQL using ASP?

---

Getting A SUM In An UPDATE
I'm trying to update a "parent" table with the sum of data in a "child
table". To illustrate, let's say our database was modeled around a book:

book
-> chapter
-> page
-> attachment (pictures)

The -> represents a one-to-many relationship (e.g. a book has chapters).
Each attachment has a filesize and the parent book has a total_size
(denormalized) column that is the sum of all it's children attachments. I'd
like a query to update total_size in the book table.

Below is what I've come up with thus far. Obviously, it's not working, and
hence my post. The nested SELECT query works fine, but I'm receiving a "You
can't specify target table 'book' for update in FROM clause" error. Code:

---

Update SQL
I am trying to code a bit of a java app up, and I am sort of learning as I go, so please bear with me for my lack of experence.

I set a table in a database using the following syntax:

CREATE TABLE Instructor(
       FirstName         VARCHAR(20)      NOT NULL,
       Surname           VARCHAR(20)      NOT NULL,
       Title             VARCHAR(20)      NOT NULL,
       DOB               DATE         NOT NULL,
       Address1          VARCHAR(50)      NOT NULL,
       Address2          VARCHAR(50)      NOT NULL,
       Address3          VARCHAR(50)      NOT NULL,
       PostCode          VARCHAR(50)      NOT NULL,
       Telephone         INT          NOT NULL,
       Mobile            INT          NOT NULL,
       Email             VARCHAR(50)      NOT NULL,
       
       PRIMARY KEY(FirstName, Surname));

I create a record with some information (firstname = emma, surname = newton)...

I am then trying to update a record that has been created as follows: Code:

---

How To Get Key ID For An UPDATE
I need to update a series of fields of a table based on some conditions and get the primary key of affected fields for post-processing the updated rows. Due to the fact that update conditions varies, I do not know what the key field(s) is(are) in advance, so what I want is, when making the update, and in the same transaction, get the primary key of the row that was updated.

What I'm doing now is going through a series of 2 queries per transaction, the update and then a select searchig for the updated row(s) with the updated field content.

I'm working in PHP 5.1.6 under fedora 6. In a worst case, the update will affect up to 10 rows, so I'll need to get all 10 primary keys... the query I'm running is:

$db_query_p3 = "UPDATE message.outgoing SET
pool_lock = '$p3_lock',
engine = '$engine_id',
pool_engine = '$pool_engine'
WHERE pool_status='10' AND (pool_lock < '$lock_expired' OR ISNULL(pool_lock)) AND ISNULL(engine) AND ISNULL(pool_engine) LIMIT 10";

but how to capture the primary key?

When making an insert the transaction return the key with something like:
$insert3 = (@dba_insert_id($db_query_p3))

---

Update
I want to update 8 rows in a table but these are spaces over the 25 tables. There is a common factor in them all but can't think of a way of doing it.

---

UDF With Update
I have a lookup table that holds two columns - a key value and another
column containing items. For e.g.

Col1 Col2
1 10
1 15
1 12
2 20
2 15

Anohter table has to be updated with col1 and the max value of col2
for every col1. So ti should look like
1 15
2 20
....

Right now i have a sQL statement that gets the max of the first table
and then does an update on the second. I was thinking about making it
a single query. Multi table update doesnt work here as the order of
updates is not guaranteed.

So i wrote a UDF that will get the maximum value of a column for any
table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The
maximum value of col2 in Table1 where col1 = 1).

When i use this in my update
update table2 set column = maxfunc('Tab1', 'Col2', 1)
where column2 = 1;
hangs mysql.
Has anyone tried UDF with update statements?

---

Update
Is it possible to do update a row with out first doing a separate query... sort of like += in perl or the sum() function?

---

UPDATE
I have in table 2 rows with same record only field 3 and 4 is different.

field 1, field 2, field 3, field 4
_______________________________________
salcin, elva, 23, 0
salcin, elva, 0, 45


so how can I update or... to have only one row with record like:

field 1, field 2, field 3, field 4
_________________________________________
salcin, elva, 23, 45

So just to get 1 row and where is value 0 and field 1 and 2 same to get "new" value

---

UPDATE
I have in table 2 rows with same record:

field 1, field 2,  field 3,   field 4
_______________________________________
salcin,   elva,     23,         0
salcin,   elva,      0,         45

so how can I update or... to have only one row with record like
field 1,  field 2,  field 3,   field 4
_________________________________________
salcin,    elva,     23,         45
     

---

Update
"UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1";

With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?

---

Update 5.0 To 5.1 Or 6.0 ???
i just upgraded to latest mysql 5.045, should i go to 5.1 or 6.0?? is it worth the pain, do i need to change passwords format like from 4 to 5?

---

Update
I have about 1000 updates required on a table.

The syntax i know is for example

Update t1 set b_id=9264 where b_id=4259;

and then the next one would be

Update t1 set b_id=9358 where b_id=2253;

etc etc

Is there any way i can run an update with multiple instructions?

---

UDF With Update
I have a lookup table that holds two columns - a key value and another
column containing items. For e.g.
Col1 Col2
1 10
1 15
1 12
2 20
2 15

Anohter table has to be updated with col1 and the max value of col2
for every col1. So ti should look like
1 15
2 20
....

Right now i have a sQL statement that gets the max of the first table
and then does an update on the second. I was thinking about making it
a single query. Multi table update doesnt work here as the order of
updates is not guaranteed.
So i wrote a UDF that will get the maximum value of a column for any
table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The
maximum value of col2 in Table1 where col1 = 1).
When i use this in my update
update table2 set column = maxfunc('Tab1', 'Col2', 1)
where column2 = 1;
hangs mysql.
Has anyone tried UDF with update statements?

---

Update
I have an address database "address", and I need to update the field "street" row by row for some reason, then when I use

update (select street from address limit 0,1) set street="High Road";

I have a error

nor

update address set street="High Road" where Street= (Select Street from address limit 0,1).

---

Update 4.x To 5.0
Has anyone made this update?

I'm running on FC4 for our corporate intranet and am running the non-rpm distro of MySQL 4.1.

What is the easiest way to update to 5.0? I don't really see any update scripts or anything to make this update? Is it as easy as untarring the package - installing it just like I did 4.1 and then copy the data files over (certainly it can't be that easy?

---

Update
[MYSQL]

"UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1";

[/MYSQL]

With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?

---

Update
I want to update the country field in my clientinfo table with phpadmin where country is empty.
The field is:
Name:country
Type:varchar(250)
latin1_swedish_ci
Atributes: -
Null: Yes
Predetermined: Null

What is the mysql to do that?

---

Update
I have two tables status and confirm status.
Status has these fields
authno,user,status

confirm_status
authno,user,status

now i want to do a update script that updates the status in the status table based on matching authno from confirm status???

so im trying to update the status field in the status table based up matching authnos from confirm_status and the status table??

---