MYSQL :: Insert Then Update That Record

Update Record, Insert New If Doesn't Exist
I have a table that contains a list of parts used in a repair. The table contains foreign keys to the part and job concerned. it also contains data of time taken and status.

This data is read into a gui and the user can update the time taken and status. The user can also insert new parts in the gui.

is it possible to write an sql statement that will update the existing records and insert new ones if none already exist?

at the moment I'm having to try and update each record one, at a time and check the number of rows modified, if zero then insert a new record. all of which is done within the application. It works but doesn't seem very elogent...

How To Insert And Update Multiple Values In One Record
How do I insert and update multiple values in one record. e.g.

1. How do I insert "dogs,cats, snakes" in one record
2. If I have "dogs, cats, snakes" in one record and I want to update it and add 'pigs' to make it "dogs, cats, snakes, pigs", how do I do that?

Insert Record
i have this table with auto increment id

DROP TABLE IF EXISTS `junta`.`subdominios`;
CREATE TABLE  `junta`.`subdominios` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `nome` varchar(45) NOT NULL default '',
  `descricao` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

if it is an auto-increment id.How to insert a record without specifying it?

Insert Record
I have a table "brokers" with a field in the table "value_votes",
I want to add new values to the "value_votes" field with the new number adding to the existing number and not creating a new row with the number.

Insert Record And Return Id
lets say i have my db setup as such

user
id | name | email | default_pic

picture
id | user_id | name

if each user can have multiple pictures with one being their default it would be helpful to insert their account and default picture into the database and update their pic. right now im doing something like this

//insert user into db
//insert picture into dabase
//select most recent picture
//update default_pic with most recent picture

is there any way to do this
//insert picture into db and return the id just created
//insert user into database with pics id we just got

running these 4 sql commands as shown above seems to be slowing my page down a great deal.

Can I Insert A Record Via Email?
I have a form which is filled out, when it is submitted I get sent an email in the format of a pre-filled html form, In the email I have a button which inserts the dataset into the mysql table.
I get the email perfectly, with the data the user entered in a form, however when I press the submit button (which takes my to the page to insert into the table this happens (screenshot) As you can see, none of the variables seem to be passed over (The forms method is POST) and each variable goes iknto the POST array

PHP

<?php$IP = $_POST["IP"];$Name = $_POST["Name"];$Email = $_POST["Email"];$Website = $_POST["Website"];$Phone = $_POST["Phone"];$Display_Email = $_POST["Display_Email"];$Website_URL = $_POST["Website_URL"];$Display_Phone = $_POST["Display_Phone"];$Age = $_POST["Age"];$Hourly_Rate = $_POST["Rate"];$Location = $_POST["Location"];$Other_Location = $_POST["Other_Location"];$Eye = $_POST["Eye"];$Hair_Color = $_POST["Hair_Color"];$Build = $_POST["Build"];$Hair_Length = $_POST["Hair_Length"];...$Services_Offered = $_POST["Offered_Service"];$Rules = $_POST["Rules"];$sql = "INSERT INTO PROVIDER (IP_Address, Name, Email, Website, Phone, Display_Email, Website_URL, Display_Phone, Age, Eye, Rate, Location, Other_Location, Hair_Color, Build, Hair_Length, Bust, Hips, Cup, Waist, Height, Availability, Grooming, Services, Rules)VALUES ('$IP', '$Name', '$Email', '$Website', '$Phone', '$Display_Email', '$Website_URL', '$Display_Phone','$Age', '$Eye', '$Hourly_Rate', '$Location', '$Other_Location', '$Hair_Color', '$Build', '$Hair_Length', '$Bust', '$Hips', '$Cup','$Waist', '$Height', '$Availability', '$Grooming', '$Services_Offered','$Rules')";echo $sql;mysql_query($sql) or die(mysql_error());echo "<h1>Updated, approved.</h1>";?>

How To INSERT A Record When NOT EXISTS
How to INSERT a Record when NOT EXISTS

My current code has a compile error:

DECLARE MyVariable integer;

IF NOT EXISTS (SELECT * FROM MyTable WHERE RecordID = 1) THEN
INSERT INTO MyTable (Column1) VALUES ('Some value')
SET MyVariable = LAST_INSERT_ID()
END IF

MySQL tells me that this has a syntax error. Help!

How can i implement this properly? What's the best way to do it?

Insert Using Some Data From Another Record
i have a table with four fields: (one, two, three, four) the first three fields make up a composite key. i want to insert multiple records that just changes the value for field "one". example:

current record:

(11, 22, 33, 44)
i want to add the record (22,22,33,44), then also add (33,22,33,44), also (44,22,33,44), etc.

i use the innoDB version. is there any easy sql that does this?

Insert Record At A Specified Position
I have a table having 2 columns ie,

1. id : primary key, auto increment
2. name

I want to insert a new record at a specified posion in the existing table having some records Suppose I hvae 5 records and I want to insert a new record at position 3. How can I do that in MYSQL . I'm using version 4.

Getting Next, Previous Record After INSERT
When I insert a record in a given index order I want to be able to traverse to the previous or next record of that index order, before or after the one I inserted. So for example if I insert a record with an address of "20 Heather Court" using an address index order, I want to be able to push a Next button (on a Web HTML form) to go to the record corresponding to "21 Heather Court" or, if I press the Previous button I
I want to be able to go the record corresponding to "19 Heather Court". Assuming of course that both the Next and Previous records exist before I insert "20 Heather Court".

If I change the index order to customer first name and insert a new record for a customer named "Harry" I then want to be able to push the Next button and see the record for a customer named "Jan" or if I push the Previous button I likewise want to pull up the record for a customer
named "Garry". Assuming that the records for Jan and Garry already existed before I inserted the one for Harry and that they became the Next and Previous records with respect to Harry's after the insertion.

Using an auto increment field does no good because every time I change the index order any kind of sequential relationship between the values in the auto increment field gets totally messed up.

I have also tried statements like " SELECT * FROM Customers WHERE Customer_ID <349 ORDER BY `Customer_ID` ASC LIMIT 0 , 1" without success if there are 10 records before the one with a Customer_ID of 349. A LIMIT of 0,1 will start at record 0 for only 1 record. I want to pull up the record immediately preceding the one with a Customer ID of 349.

In short is there some way to tell which record is Next or Previous to one that I insert under a given index order?

Cant UPDATE The Record. Help
$connect=mysql_connect("localhost","root","") OR die('Coould not Connect to MySQL because <b>'.mysql_error().'</b>');
mysql_select_db("guestbook") OR die('Could not Connect to MySQL because <b>'.mysql_error().'</b>');
$query="UPDATE redorartiaga SET fname={$_POST['fname']},mi={$_POST['mi']},lname={$_POST['lname']},address={$_POST['address']},birthdate={$_['bday']},contact={$_POST['contact']},email={$_POST['email']},jobtitle={$_POST['jobtitle']},company={$_POST['company']} where id={$_GET['id']}";
mysql_query($query);
mysql_close();

what seems to wrong to my syntax. i cant determine whats wrong, because mysql doesnt give me any error message but nothing updates to my record.

Retrieve Record Number Of Insert
I am using ASP/VBscript.

How can I retrieve the record number of a record I have just inserted without doing something ugly like max(ID)?

Thanks in advance for suggestions. Steve Davis
hey.you AT HaHaHa.com.au
http://www.HaHaHa.com.au/weblog

Me? I can't even spell ASP!

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination

Finding Primary Key Of Record After INSERT
I have a mysql db with with autoincrementing primary keys in each table. I
want to allow a user to add a record using a php webpage. Once the page is
added how can I re SELECT that record (for the user to verify) if I don't
know what the primary key was when it was inserted?

Record Update/Edit
I have a number of tables that are linked together. If a record record is created in the "main" table, atleast one record or more is created in the other tables.

My users have requested a edit function and I was wondering which of the below methods would be best.

1. Delete the main record and all record relating to it in all the other tables. Then creating new records for the tables with the new data.

OR

2. Check if the the data has changed, and if it has either create a new record if one does not already exist or update if it already exists. Or if data did exist and no longer does delete the record.

For simplicity sake the first one was the option I would go with but I am not sure if it would create more headaches in the long run.

Update Same Table With More Than One Record
I have a forum table which I need to perform the following update

1) I have an original message which I need to change its online status into DISABLE 'D' in my "status" field as follow single command:

UPDATE forum SET status = 'D' where message_id = 1 and parent_id is NULL;

*the parent_id field is NULL because it is a parent thread...

However, it has some child threads to this message...therefore I must force the all the child thread to change the status into 'D' also. So I did a second sql query as follow:

UPDATE forum SET status = 'D' where parent_id = 1;

*parent_id = 1 means it points to the parent message_id = 1

My question is

DOES anyone know a good sql query that will combine these two queries into ONE single query?

Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)

UPDATE Record & Upload Image
Am creating a backend CMS and want to give users the ability to update an article and upload a new image if they wish or keep the original. Am not sure where to even start with this as have only ever used image upload with INSERT.

Create Trigger To Add UUID() Value To A Field When Record Insert
I added a field to my users table, the field name is uuid, and I want to create a trigger that activates on insert and set the uuid value for that new inserted record to the function UUID(). I tried this, but no success

MySQL
CREATE TRIGGER uuid_users_insert BEFORE INSERT ON `users`FOR EACH ROWBEGINSET NEW.uuid = UUID();END;

I get this error:

Quote:

ERROR 1064 (42000): 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 '' at line 4

How To Insert New Records Based On Previous Record Values?
I have
TABLE:

year
value

It has for example,

year, code, vlaue
2007, 33, 4883
2006, 33, 4883
2007, 32, 4883
2006, 32, 4883
2008, 31, 4883
2007, 31, 4883
2006, 31, 4883

I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%.

My several attempts have led to this but which does not work.

UPDATE TABLE a
SET a.value = b.value * 1.05
WHERE b.year = 2007
AND code NOT IN
(SELECT code
FROM TABLE b
WHERE year =2008
AND value !=0)

HOWTO Insert A Record With A Large Integer Array?
I'm a newbie to MySQL, ok, also to Database.Here's my problem:
I've got records to insert from files, each file stands for a single record. And each record contains an Integer array, the size of this array remains unknown until I've through the file, particularly, the size could be large.

So I turn to BLOB, but I found it's confuse to retrieve the Integer Array from the database record, The Integer arrays shrink, the '0x00' seems to be automatically omitted by this column type.

I wander, is BLOB the right type for this situation, or HOW can I use it correctly.

UPDATE Step Thru Every Record W/out Unique Id Field
How can I update every record of a given table one by one. I need to update a field (date) with a different, random date for each record.

loop {

generate random date;
update one record with new date;
goto new record

}

Problem. Table does not have a unique id field

table (item, date)

peaches - 2003-05-13
pears - 2003-11-24
peaches - 2004-01-04

the table is a given... meaning I have to work with it the way it is.

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

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()."'
, Ɔ'
, Ɔ'
, Ɔ'
, Ɔ'
, '".microtime()."'
, Ɔ'
)";
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)?

Auto-increment And Update/insert
I have the following problem with a MySQL update/insert.

my problem lies in the fact that I have to insert a new row between 2 existing rows. the table is sorted by ID and has ID as auto-increment so I cannot insert a new row between 2 rows as inserting will assign the insert a new ID number...

I figured now I have to do an update and then loop till the end of the table and then do an insert at the end..

INSERT And UPDATE Similar Statements?
At least as I know it, INSERT goes like:

INSERT INTO table (Col1, Col2) VALUES('Hello', 'Goodbye')
While UPDATE goes like:

UPDATE table SET Col1 = 'Hello', Col2 = 'Goodbye'
Is there any way I could make them the same format, such as:

UPDATE table (Col1, Col2) VALUES('Hello', 'Goodbye')
?

Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion.

I am think of using this:
INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3

is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.