Insert On Duplicate Key Update Question?

Insert On Duplicate Key Update
Is it possible so do something like this

"insert * from TempTabel on dublicate key update 'all fields'; "

So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.

---

Insert A Duplicate Of An Existing Row
I have a table with an auto-increment columns (absid) and I want to take a row with a certain absid and re-insert it as a new row with a new absid (but all other columns as from the copied row). Running mysql 3.23.55. Might something like the following work?

insert into mytable (absid,*) values 0 select * from mytable where absid=20;

or something similar? With 3.23.55 I suspect it won't work anyway but I have no access to a version 4 installation and wouldn't want to upgrade if what I want to do is inherently not possible.

---

Replace Or Insert Duplicate
Just wondering how to write less PHP code, if MySQL does the job for me...

I have this really simple table:

CREATE TABLE `customers` (
`id` bigint(20) NOT NULL auto_increment,
`name` char(50) NOT NULL default '',
`address` char(50) NOT NULL default '',
`phone` char(9) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Well, the point is, when I want to insert a new customer I do the following (in PHP)

- search for a customer by his name:
SELECT id FROM customers WHERE name='$name'
- if I get rows, the customer exists, so I update his data:
UPDATE customers SET name='$name',address='$address',phone='$phone' WHERE id='(the id)'
- if not, the customer doesn't exists, so I insert him:
INSERT INTO customers(name,address,phone) VALUES('$name','$address','$phone')

So... is there a way to do all of this with a simple MySQL statement like REPLACE or INSERT ON DUPLICATE KEY UPDATE ???

---

Update If Duplicate
does anyone know how to do an update on a table only if there's duplicate entry in this table using a query?

---

ON DUPLICATE KEY UPDATE
i have query to set a login time, or update the time if a user is already on the database.
$query = mysql_query("INSERT INTO logged (username,time) VALUES ('$userid','$time') ON DUPLICATE KEY UPDATE time='$time'") or die(mysql_error());

and the erorr i get back is 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 'ON DUPLICATE KEY UPDATE time='1110251002'' at line 1 im running MySQL 4.0.23-standard.

---

On Duplicate Update
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query the database seems to have the correct entries in it but Im getting an error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Duplicate entry '0-thisURL' for key 2
<cfqueryparam value="#now()#" cfsqltype="cf_sql_date">)
v>

Am I way off the mark here? Like I say the entries in the database seem sorrect but I still get the error.

---

DUPLICATE KEY UPDATE
Like many tables, mine has a PK with AUTO_INCREMENT set. I would like to roll my UPDATE and INSERT sprocs together into one to keep things clean.

I'm having a hard time getting my head around how this 'ON DUPLICATE KEY UPDATE' works without checking for a null PK or -1 or something like that.

In other words, if I'm inserting new data, I don't have the PK value and my table has it's PK column as AUTO_INCREMENT, so how do I handle that? Pass it NULL for the PK on INSERT and a valid PK on UPDATES?

In the past I have done this with an IF ELSE block to check for existence, but the MySql docs lead me to believe this is built in somehow.

---

On Duplicate Key Update
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?

---

How To Handle Duplicate Keys In Insert
I have the following table definition:

CREATE TABLE `suggested_synonyms` (
`Last_Name` varchar(255) NOT NULL,
`Synonym` varchar(255) NOT NULL,
PRIMARY KEY (`Last_Name`,`Synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The table stores mapping from a last name to a synonym.
I need to insert multiple rows into the table. Something like:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath');
the problem is that some of the mappings may already exist in the table.
I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE;
As I need to specify the columns to update.

Is there a way to insert multiple rows in a single query and still be safe from duplicate rows?

---

Insert/Ignore/Duplicate Entry
PHP

$sql="INSERT INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If I leave this query as is and I already have a LOGIN NAME that is a duplicate, I get a messy error message something like this...
Notice: Query failed: Duplicate entry 'jon' or key 2 SQL: INSERT INTO ...blah blah...

MESSY!!!

So I modified the query to:

PHP

$sql="INSERT IGNORE INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If there is a duplicate, I dont get the messy NOTICE error, which is fine. But if I already have that entry in my USER table, it will precede to get my next available MYSQL_INSERT_ID.

I dont want that to happen if it finds a duplicate using the IGNORE. I just want it to get that duplicate records ID instead, but if it doesnt find a duplicate record, get the last INSERTED record using the MYSQL_INSERT_ID()

---

V4 Error On Insert With Duplicate Entries
It appears to me that MySQL version 4 returns an error messge when doing an
Insert that results in duplicate entries. Version 3 did NOT return an
error - it dropped the duplicate entries and ran to completion. Version 4
seems to STOP when it encounters a duplicate entry, so that the records
before the duplicate are inserted and the records after the duplicate are
not inserted.

3.22.27.1 - previous ver MySQL that did not return error
4.0.16.0 - current ver MySQL that returns error.
Running on Red Hat Linux 7.3

Is there a way to change this behavior to the way it was handled in version
3? Are there configuration settings on MySQL that control this?

---

V4 Error On Insert With Duplicate Entries
It appears to me that MySQL version 4 returns an error messge when doing an
Insert that results in duplicate entries. Version 3 did NOT return an
error - it dropped the duplicate entries and ran to completion. Version 4
seems to STOP when it encounters a duplicate entry, so that the records
before the duplicate are inserted and the records after the duplicate are
not inserted.

3.22.27.1 - previous ver MySQL that did not return error
4.0.16.0 - current ver MySQL that returns error.
Running on Red Hat Linux 7.3

Is there a way to change this behavior to the way it was handled in version
3? Are there configuration settings on MySQL that control this?

---

Update If Duplicate Found
Im currently running a statement that inserts a new row into the table if a duplicate row is not found:

PHP

$db->Execute("INSERT INTO tee_time (          SELECT $locationid AS locationid,          (SELECT courseid FROM course_conversion WHERE coursename = '$currcourse' LIMIT 1) AS courseid,          '$currcourse' AS coursename,          '$currtime' AS teetime,          '$currdate' AS teedate,          '$currcost' AS cost,          NOW() AS grabdate          FROM tee_time          WHERE coursename = '$currcourse'          AND teetime = '$currtime'          AND teedate = '$currdate'          HAVING COUNT(*) = 0)");

The problem is if a row is found I need it to update that row instead of just skipping it. On duplicate wouldnt work because teetime and teedate can be the same in multiple rows.

---

ON DUPLICATE KEY UPDATE Syntax
I have two tables with identical columns. I want to update records in tbl1 with record information from tbl2.Code:

INSERT INTO tbl1 SELECT * FROM tbl2 ON DUPLICATE KEY UPDATE SET tbl1.col=tbl2.col, tbl1.col2=tbl2.col2

I seem to be missing something here...

---

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

---

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

---

URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly.

I have a query:

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a")

What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a".

When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time.

So I now have two listings that have a groupname of "a" and "b".

Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc...

How can I do that.

I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones.

Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query.

Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")

---

Using ORDER BY With UPDATE To Resolve The Duplicate Key
I found this page, which is relevent to my problem:

http://www.mysql.com/news-and-events/newsletter/2003-09/a0000000224.html

I followed the suggestion and altered my query to this:

UPDATE library_categories SET lft = lft + 2 WHERE lft > 1 ORDER BY lft DESC

and I get this error:

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 ' 1 + 1, + 2)' at line 1

Is there a way to get this query to work?

---

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

---

ON DUPLICATE KEY UPDATE With An Old Version Of MySQL
ON DUPLICATE KEY UPDATE is new as of MySQL 4.1.0, but the web server
that I'm using has MySQL 4.0.24

How would I implement ON DUPLICATE KEY UPDATE with an old version of
MySQL?

---

LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import.

But I don't think I can use it so I want to check here first before I completely nix the idea of using it.

Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand.

Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file?

(i hope some of that makes a little sense?)

If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:

---

Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate
update" clause with insert command.

my table "data" has two columns, field1 and field2, where field1 is the
index and is "unique".

when I run

insert ignore into `data` ( `field1`,`field2`)
values
(1,2)
(2,6)
(2,9)
(5,1)

I got
1,2
2,6
5,1

because the third value pair, (2,9) was ignored due to duplication in
field1.

here is what I want to do: when inserting new record with duplicate
index, I want the existing record gets updated. in the above case, I
want the output look like the following after the insert command:
1,2
2,9
5,1
after searching mysql online document, I only found the "on duplicate
update" clause can do something similar, however, all the examples only
show setting the duplicate record to somthing unrelated to the new
values.

---

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 INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0:

idcol lft (unique)
1 1
2 2
3 3
4 4

-> when I try to update the lft column using :

UPDATE unique_example
set lft = lft +1
where lft > 1;

I get following error message :

Duplicate entry '3' for key 2

Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:

---

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?

---

UPDATE Or INSERT
Kind of worked myself into a weird place in my current application. Where I find myself at is with a single form that is supposed to handle both updates of existing rows and insertions of new rows. The way I want this to work is:
1) If the ID is specified and references a valid row, update that row
2) If the ID is specified but does not reference an existing row, insert a new row
3) If the ID is not specified, insert a new row

Now, (3) is easily done in my application - just test if the ID was specified by the user. Simple. Nothing to it.

What I'm wondering is if there is a way to do both (1) and (2) in a single MySQL query and receive feedback such that I can in my application inform the user whether an update or an insert took place. I could do this with two seperate queries (query for the ID; if I find the row update it, otherwise insert a new one), but I'd like to do it with a single one if possible (mostly because I want to expand my skills with complex SQL queries).

---

Update Or Insert Sql
I am using this query to update tableone based upon the values in tabletwo. I really like this query because it works very fast.

PHP

UPDATE tableone n, tabletwo c
   SET n.sec  = c.sec,
       n.cat = c.cat,  
       n.published = 1
WHERE n.section = c.section
   AND n.category = c.category


This works great however I cant seem to get it to insert or append if the data already exists. It just overwrites the data.

My difficulty is that I need certain columns to be updated (overwriting the data) and other columns to be inserted or appeneded to (adding to the data).

Is a stored procedure the best way of doing this?

Or, is there a way of adapting my sql to cater for this?

---

Need To Use UPDATE Instead Of INSERT INTO
I need to UPDATE exisiting rows and populate
the fields using only the resources in this INSERT statement.

INSERT INTO cms.project_event (projectPhaseID,sortOrder) SELECT
cms.projectTemplate_eventTemplate.projectTemplatePhaseID,
cms.projectTemplate_eventTemplate.sortOrder
FROM cms.projectTemplate_eventTemplate WHERE  
cms.projectTemplate_eventTemplate.projectTemplateID = '1'

Any clue pointing in the right direction will be of great help. Im not providing alot to go on just the to update the two fields from the other table instead of INSERTING new rows.

---

Last Update/insert
Can I know the time when the last UPDATE or INSERT occur in a table in a database?

---

UPDATE And INSERT In One Go?
Is it possible to create a query that will do an UPDATE or an INSERT depending on certain data already in the database?

Eg I'd like to record a user's vote. If this user hasn't voted already, an INSERT is called for, otherwise an UPDATE. Right now, I'm SELECTing first, checking whether the user's id is already in the votes table, and if yes, my query's an UPDATE, otherwise it's an INSERT.

This needs two hits on the DB however, and I'm someone who likes to limit the amount of DB hits as much as possible.

---

Update And/or Insert?
I'm using MySQL with PHP. I want to update a record if it already
exists. If it does not exist, I need to create a new record.

Is this done through a simple SQL command, or do I need to use PHP to
negotiate two separate UPDATE and INSERT statements?

---

After Insert, After Update
I am taking my first and last class in programming as I can't make sense of the textbook or teachers instruction (but I am determined to get at least an A or B in this class.) So if anyone could tell me in VERY simple terms how to write an after insert and after update trigger I would be very much grateful.

---

INSERT / UPDATE
I'm having trouble using the IF statement.
What I want is to do either a INSERT or a UPDATE depending on the SELECT statement.
No mather what i do, it gives me an error on the INSERT statement. Isn't allowed to have a INSERT/UPDATE statment inside a IF statement?

This is what i'm trying:

SET @param = (SELECT)

IF @param IS NULL THEN
INSERT...
ELSE
UPDATE...
END IF

---

INSERT Or UPDATE
I need using a SELECT statement, but is there a way to make this work on INSERT or UPDATE statements as well?

In other words, I can do:
SELECT MONTH(deadline) FROM projects ...

But can I do something like:
UPDATE projects SET MONTH(deadline)='11' ...
where the rest of the date stays the same, but only the month is updated?

Doesn't seem to work, and I don't see any examples in the manual comments, but it sure would be nifty.

---

Update Or Insert
I have to create a database with 8.000 entries.
It will be the top entries it will have. Now... what is faster ?
a) to create the table before with 8.000 rows and the id field from 1 to 8.000 (primary key...) and then do UPDATE's
b) to create the rows as they have to be created with INSERT's ?
In the middle of the process it will be SELECT's from other users.

---

Insert If I Can't Update
I check for the data to exist. If it does, I UPDATE, otherwise I INSERT. Is there a way to do it better, more efficiently?

---

UPDATE Or INSERT
I am looking for a solution to an update-type problem. Users enter name, address, etc etc. I have set the first_name & last_name fields to be jointly unique, so that no two records can exist with the same first and last names the same (e.g, no 2 records with 'Joe Bloggs' as first/last names.).
Now if users want to correct, say, their address, it will not allow them to since a record already exists with their first/last name.
Is there a SQL statement that will allow me to bypass the UNIQUEness constraints when other fields need to be updated?
(And related, if the UNIQUE fields are updated, it currently creates a new record but leaves the old one there. I need to overwrite the old record but keep the same ID number primary key.)

---

INSERT/UPDATE
I've got an array in PHP, which is identical to a row in mysql:

Code:

array
(
'key' => 'somekey',
'column1' => '...',
'column2' => '...',
'column3' => '...',
);

If 'somekey' does not exist in the table, I want to INSERT this array as a new row. Otherwise, I want to UPDATE the row which has the key 'somekey'.
Is it possible to do this in a single query? Cause now, I'm first INSERTing a row and if that fails, I try an UPDATE instead.

---

Insert/update
I have a table of x/y values and a set of 'new' x/y values.
For each of my new x values that already has a record in the table, I want to add the new y value to the corresponding existing y value.
If there is no x value in the table, I want to create a new record with the new x/y values.Is there a simple (atomic) way of doing this?

---

Insert Or Update If Exist
what is the sql statement that can insert or update the record if it exist.

---

Update / Insert / Delete Log
I'm wondering if there is any way to get a lot of recent update/insert/delete statements performed on a db by a particular user?

---

Insert Then Update That Record
I'm using PHP to insert contact information in to a database. Confidentiality requirements have made the design change so that the private information be entered separately. I've modified Form 1 so that it contains the public information and added a button. The button does two things, it inserts the record in to the database then draws the private information form.

My question is, can mysql give me the last row inserted so I can pass that information on to the form and use it in my update query? I tried doing it by ID in that table but the id is not assigned until after the submit button is pressed. I can do another query but thought it would be slick to do it by row.

---

If Exists Then Update, Otherwise Insert
provide a very simple if, then, else clause in MySQL?
If a customer named 'bob' is already in the database, I want to update his id. If he is not already in the database, I want to insert him.
What would be the best way to do this?
I get errors for the following:
IF EXISTS
(SELECT 1 FROM customers WHERE name = 'bob')
THEN
UPDATE customers
SET id = '007' WHERE name = 'bob';
ELSE
INSERT INTO customers(id, customer)
VALUES('007', 'bob');
END IF

---

Two Timestamps For Update And Insert
I need to record date updated which a timestamp does, but also the date inserted. Can I do this all from the mysql database, or do I need to have a variable date inserted in the php. The first option appeals to me more.

---

Simple Insert And Update
Im having a rather irritating problem..because its so simple and I just cant figure it out.In my database I have some company info and the primary key "companyID" is used to reference it in several other tables.
however in phpmyadmin I cannot figure out how to set it to automatically generate when I create a new company in the company table and then propogate to all the tables it links too.

---

Unable To Update And Insert
I have a weird problem which is i able to access to the DB, able to do a select but unable to update and insert.
This is the scenario.
The existing running db is called as 'abc'. I dump it out and the i restore it as 'abcv2' and add in few column and insert value in. Then my application able to access the db. Able to do a select but unable to perform insert and update. No error is show in my application. Application wise i not the problem as it able to do so with others db.
I suspect is the privileges problem therefore i create a user and grant all access to that user. But the same thing as unable to update and insert.
Any idea of my problem? I think if i change the database name from a dump file won't cause this problem right.
What i can do now is i get the dump file then i open it with notepad and do the necessary changes. This is working fine but i would like to know what is the cause for this problem.

---

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?

---

Insert And Update Entire Table
My website will modify records on my products table. But I need to occasionally update their qty and their prices without losing any changes already made by other users online (like qty sold).

I'm able to do that now. It works really well:

---

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)?

---