UPDATE Using SELECT

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

---

SELECT But Not UPDATE
If I have used SELECT to review a row of data from a table and then only want to allow it to UPDATE certain fields, even if the other fields were changed, would I use the UPDATE command, and choose which fields can be updated? Will this stop the other fields from being updated? I am trying to prevent people from changing certain values in the table.

---

SELECT And UPDATE
I'm a newbie in MySql, so please bear with me. What I want is to select all the informations concerning a specific article, and then to update a field of the resulting query. So, with some help I managed to do something like this

CODE$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);

---

Select Update
I am trying to populate a column in one table using a calculation from values in another table.I have got something like this.
Code:

update orders_header
set od_gross = (SELECT sum((price * qty) + vat) FROM orders_detail)
where orderref = 'COR0010-000061'

---

Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url.

PHP

UPDATE remoteurl SET hits=hits+1 WHERE id=2

SELECT url FROM remoteurl WHERE id=2

Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?

---

SELECT And UPDATE In One Order.
i would like to select some random lines from a table and count that they were selected.

My method:

SELECT * FROM table ORDER BY RAND(Now()) LIMIT 50;
while ($row = entry) do
UPDATE SET Used=Used+1 WHERE Id=$rowd[Id]

I dont think this is realy fast so i dont like it, is there a faster method?

---

Select + Update In One Statement
I have these statements.

$selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

$query = mysql_query($selectdata) or die(mysql_error());
$update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.

---

Atomicity Of A SELECT/UPDATE
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?

Given something like this table:

create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);

I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.

If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could: Code:

---

Select And Update Statement
Is it possible to combine a select and update statement in mysql? If
so, what is the syntax.

---

Select And Update Concurrently
I have a db used for shop floor data collection that I have converted from a Progress V8 RDBMS. It is recording some 50 data points from each of 6 shop floor machines every 5 seconds. The problem I have is one of incremental backup of this (the main data logging) table. In the old system, there is a LOGICAL (boolean) flag field that is set (in code) when the data records have been copied to the backup file (could be another table). I can't seem to find in MySQL documentation or in these forums, a way to select and update in the same query.

What I would like to do (in as plain english as possible) is:
Select all records from the source table where the 'copied' flag is not set, and send them to a destination (which could be a flat file or another table), simultaneously setting the 'copied' flag in the source table.
I thought of doing this in two passes, selecting the records first, and outputting them, then setting the flag, but the problem this poses is that there may (probably will) be fresh data written to the table during and between the passes. This will cause records to flagged as copied that have not actually been backed up.

---

SELECT And UPDATE In One Query
Does there exist a way to do a SELECT and an UPDATE in one query?

What I am trying to do is building a shop that´s articles are saved in a mySQL db and I wanna count the impressions of each article that is shown by e.g. a search function.

would be something like that:

article no | text | category | impressions
1 | aaaa| 1 |1
2 | bdsd| 2 |1
3 | aaaa| 2 |1
4 | bdsd| 3 |1

and so on

now I want e.g. to search for all articles that are in category 2 AND add +1 to the impressions cell of those articles that are found.

I hope it´s understandable what I am trying to do. I guess it should be possible, but since I only so far used very simple queries I am not sure how to?

---

Update With Embedded Select
What is wrong with this syntax ?  Using MySQL 3.23.51-nt
UPDATE products SET manu_release_date = (SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )
I get the error:
Error MySQL said:

You have an error in your SQL syntax near 'SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )' at line 1

---

Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't.

There is a table, s01_Baskets, that tracks the users basket throughout my store.
There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id.

What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id:

SELECT
s01_StoreKeys.maxvalue
FROM
s01_StoreKeys
WHERE
s01_StoreKeys.type = 'Baskets'
Then I can "create" a basket by inserting a row:


INSERT INTO
s01_Baskets
(
s01_Baskets.session_id
, s01_Baskets.cust_id
, s01_Baskets.basket_id
, s01_Baskets.order_id
, s01_Baskets.order_proc
, s01_Baskets.last_update
, s01_Baskets.ship_id
) VALUES (
'".session_id()."'
, &#390;'
, &#390;'
, &#390;'
, &#390;'
, '".microtime()."'
, &#390;'
)";
Then I have to update that field for next key for the next basket.


UPDATE
s01_StoreKeys
SET
s01_StoreKeys.maxvalue = '".$basket_id+1."'
WHERE
s01_StoreKeys.type = 'Baskets'
UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....)

Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?

---

SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter

$: mysql -u root -p

I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?

---

Using Limit With Select Or Update Command
How come I can do:

select field1 from database limit 10
*shows first 10 records like it should

select field1 from database limit 1,5
*It returns first 5 rows correctlly

update database set field1='first set" limit 10
*It correctlly updates the field for the first 10 records

update database set field1="top 5" limit 1,5
*I get an error that says problem with limit at ^5 check command for correct syntax

Is there different syntax for limit when using with a select statement Vs. an update command?

---

Nested Select In Update Statement
my nested Select-Update-Statement doesn't work. I think it's because the SELECT-Statement returns an empty set.. is there a posibillity to run this statement if there is no empty set and insert NULL if it is? Or is there another error at all?

CODEUPDATE proj_user_suche SET acronym = (SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"

---

Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table.

SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'

---

Limitations In Mysql Select/update Statements
I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

---

Limitations In Mysql Select/update Statements
I'm having a little problem here that seems difficult to solve - to me.

I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

I assume it's something about configuration, but unsure.

Any ideas?

---

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

---

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.

---

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

---

Combining SELECT Statements Into One SELECT Statement.
I want to take the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN authuser
ON name.empnumber = authuser.uname AND authuser.team = 'PHQ'
ORDER BY name.lastname, name.firstname;


and the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN crew_attendance_6QJ
ON name.empnumber = crew_attendance_6QJ.empno
ORDER BY name.lastname, name.firstname;


And combine them into one query that outputs all of the results both queries would output. Then order those results.

So far I have come up with:

---

Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.

---

Select From Two Different Tables Depending On Result Of First Select
I have three tables A contains three keys (there is other stuff but it's not relevent to this problem):

  it's own pk,
  fk to table B
  fk to table C

if the table B fk is not zero then select various values from table B,
if the table C fk is not zero then select varios values from table C

The two fk are never both zero and never both non zero

The best query I've got so far is

select distinct l.job_parts_id, l.part_id, l.oil_id, l.quantity, l.purchase_price, l.sale_price,

p.part_no, p.description, p.supplier_id,
o.part_no, o.description, o.supplier_id

from job_parts l, parts p, oil o
where task_id = 3
and p.part_id = l.part_id
or o.oil_id = l.oil_id

order by job_parts_id;

the problem at the moment is that when the fk to table c is zero, the result set still contains the values from it's selected fields, I'm trying to work out how to use an IF statement to sort this out,

---

Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends.

Will like to know:

Assuming I have a table with 8 columns.

When I am querying these records with specific WHERE conditions,
will SELECT * be faster, or SELECT (3-5 x columns) be faster?

---

Select Lastest Input To Data Base And Select Ordering From Last Member To First Member, Limiting Output Diplay To A Specific Number
how to display the latest members that signup to website, a query that that can select ordering them base on the last registered member to the first... then limiting output by say 50 members.

a sql statement that can select lastest input to data base and select ordering from last member to first member, limiting output diplay to a specific number

---

SELECT COUNT(*) Or SELECT .. LIMIT 1?
If the purpose of a query is to see if atleast 1 record exists which would be faster?

SELECT COUNT(*) or SELECT .. LIMIT 1?

Both would use a WHERE clause.

---

What's The Best SELECT In This Case / How Can I Make This SELECT ?
well, step 1, I need to get a few ids like:

PHP

SELECT id FROM $table_one WHERE this_field=$that_value // this is OK


I will get multiple rows from this command

Then, I need my main SELECT command (that i need to use) like:

PHP

SELECT * FROM $table_two AS t2 LEFT JOIN $table_three AS t3 ON t2.pid = t3.pid WHERE t2.pid = (all the ids that I have got from my first SELECT)

---

Difference Between Select * And Select Distinct *
What is the difference between SELECT * and SELECT DISTINCT *
Which one is faster?

---

Select Constraint (select ... Where X) If 'where X' Isn't Specified
I'm working on a site where records (say articles) can exist in one of three states: active, archive, trash. I'm doing this so that I can implement soft delete and non-destructive action.

For some brief background, we used to simply have an 'archive' column with enum('n','y'). Which meant that every query had to be appended with "where archive='n'". Later we modified the architecture of the application be more robust so that you could publish records (basically duplicate the records into a table called articles_instance; so you can publish as many versions of a master copy as you want). This worked well enough that when I decided to implement soft-delete, I decided to move the deleted record into the articles_instance table. I created a new column ('state' again) with 'publish', 'archive', and 'trash'.

So whenever I query the instance table, I opt in "where state='publish'" instead of having to opt-out, like before ("where archive='n' and trash='n'". This was better, but I realized that I had to save the old record id in a column 'old_id' because I wanted to be able to send the row back into the regular 'articles' table if the user chose to undo the action or unarchive an article. I decided to keep the old id, rather then send it back with a newly generated id because there were certain relations I needed to keep, like associated audio files with the article (temporary broken links aren't as much of a problem because you can only see the audio when you look at the article; when the article is permanently deleted, then I can remove the relations).

This approach works, but I feel it's messy, and with all the code I'm writing to shuttle the rows back and forth, I feel as if there's only more chances for error. I like the simplicity of simply setting a state in the master 'articles' table. But then I have the problem of having to specify "where state='active'" in all of my queries. If I don't do that, I risk pulling all possible articles, regardless of state, causing strange problems. So here's the latest breakdown:

The articles table has a 'state' column with enum('active', 'archive', 'trash').

The articles_instance is now called 'articles_publish' and no longer has a state column. So it is used exclusively for published articles.

It boils down to this: Is there a way, in MySQL 4 (I don't know the exact version, but I can find it if necessary) to constrain/modify all of my select statements to have an implicit "where state='active'" if a state isn't explicitly specified? If I could do this, I feel I could have the best of both worlds: have two tables with specific needs, where one table has data that can exist in different states, and I wouldn't need to move rows back and forth, possible causing problems. And if I implement the constraint, I don't have to worry about going through and changing a lot of existing code and making sure the problem doesn't crop up in the future.

I am providing all this background in the hope that someone might just suggest a better and more efficient way of doing this and tell me that a select constraint is entirely unnecessary . I might also be convinced that using MySQL as a crutch in this case might be a poor idea and could potentially confuse programmers down the line (although there's only two of us).

If it's not already clear, I'm new to all of this, so I am not really familiar with best or standard practices when it comes to these sort of things. If anyone wants to refer me to any resources (books or articles) about versioning/publishing records or database design in general, I'd love to hear them.

Thanks!

---

Select With Select In Where Clause
This is a very simplified SQL statement intended to reproduce an error, not do anything interesting:

select * from value where id in (select id from value where id=1000000);

It fails with:

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 'select id from value where id=1000000)' at line 1

However, the following works

select * from value where id in (1000000);

---

Select In Select Statement
Could any give me an example of how this works please as for the life of me cant get any results.Basically I have 3 tables, I record home team and away team results in the RESULTS table and up and coming fixtures in the FIXTURES table.

Im wanting to get all my home teams previous results where they are playing at home in an upcoming fixture but also want to return for the same fixture the away teams previous results, I dont even need TEAM table to be honest but thought may be easier to create a join between the two tables.

---

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:

---