sql update,set?

[Cabbit]

$query = "UPDATE `database_table` SET `thingie` = '$thingie' WHERE `id` = '$id'";

I have a script to update a datebase, is there any way to make it create a new record if it can find one to update?

 

[Dawdles]

You'll need to query to see if there is an entry first:

SELECT ID
FROM TableName
WHERE ID = $ID

Then see if there are any results. If so, you can perform your UPDATE. If not, do an INSERT:

INSERT INTO TableName
VALUES (Value1, Value2)

*d

 

[Cabbit]

i have been reading about REPLACE INTO is that viable?

 

[Dawdles]

That appears to be a MySQL 5, and seems to do exactly what you want. However, from the MySQL Reference Manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

So it's like a DELETE and INSERT all in one go. Deletes the row if there is one, then INSERTs a new one. Might be what you want, but you'll lose any information in the fields you are not populating in that transaction.

Personally, I'd got for a more standard approach like I suggested in the previous post. Much simpler.

 

[plinden]

I've forgotten much of what I knew about MySQL, but if I recall correctly, you have a couple of choices:

IF EXISTS (SELECT * FROM database_table WHERE id='value')
    UPDATE database_table SET (...) WHERE thingie='value2'
ELSE
    INSERT INTO database_table VALUES (...)

or

UPDATE database_table SET (...) WHERE id='value'
IF @@ROWCOUNT=0
    INSERT INTO database_table VALUES (...)

The second is supposed to be more efficient if you have large tables.

You should also parameterize your SQL. If that's what you're using in your php scripts, you're possibly leaving yourself open to SQL injection. For instance, if someone manages to pass in "'; DROP table 'database_table'; commit;" (or something like that) as $thingie, you're in trouble.

 

[Cabbit]

php afide should it look like

$query = ("IF EXISTS (SELECT * FROM `table` WHERE `table_id` = '$id')
    UPDATE `table` SET `item` = '$item' WHERE `table_id` = '$id'
ELSE
    INSERT INTO `table` VALUES `table` = '$id', `item` = '$item'");
if ( !mysql_query( $query ) ) { 
	echo mysql_error();
    echo "There was an error, please try again."; 
}

 

[ChrisA]

Then see if there are any results. If so, you can perform your UPDATE. If not, do an INSERT

That is absolutely HORRIBLE advice. One of the worst things you can do is to (1) querry the DB, (2) Hold the result in local variable then (3) Make a change to the DB based of the held data.

The reason is that while you are holding the data the DB can change. You have to think about what if 15 copies of the program were all running at once. Yes this happens if you are backing up a web site with a DBMS and you have 15 users.

You can TRY and solve this problem with locks. Get a lock before the first query then after you make the change release the lock. But then, only one of those 15 users can access the data base and that monster 8-core server you paid for becomes a wast of money while 14 of your users wait in line.

You see a lot of code like this, that holds DB info in a local varable used for small-time web sites. With low traffic volume these race conditions don't happen much so they think everything is fine. There are lots of ways people who don't understand the theory of databases can get them selves in trouble. The other is with poorly normalized table designs where data are stored redundantly and the copies can get out of sync.

 

[stndn]

insert, on duplicate update


-stndn.

 

[belvdr]

In any RDBMS webapp, we always do an UPDATE, and if it errors out, then we INSERT, as in plinden's second option above. This is a much more efficient option than querying, counting, then doing something (update/insert). You might as well try the UPDATE first, and if a record exists, you've done this in 1 step versus at least 2 any other way.

 

[Cabbit]

sorry for the late post, i am still having problems with this

$query = ('INSERT INTO `key` (`id`, `story_id`, `contains`) VALUES (\'\',\''.$id.'\',\''.$cont.'\')
  ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `contains` = '.$cont.';');

This is what i got so far by following the documentation and i used last_insert_id as my id table is a auto increment. but it keeps just making a duplicate of the last row just with a different id.

 

[SrWebDeveloper]

This is what I use, it is tested on MySQL 4 and should work fine on 5 also.

After the transaction this conveniently returns a column named "Action" which is either "Inserted" or "Updated" and another called named "ID" which is either the last record ID if inserted, or current ID if updated. You adjust the insert/update criteria in the below example, run it twice on an empty table and you'll see the insert on the first run, then the update.

SET @previous_id := NULL ;
INSERT INTO test( id, note ) VALUES ('1', 'new') 
ON DUPLICATE KEY UPDATE id = IF( (@previous_id := id) <> NULL IS NULL , VALUES (id), NULL ), note = values(note);
SELECT if( @previous_id IS NULL , 'Inserted', 'Updated' ) 'Action', if( @previous_id IS NULL , id , @previous_id ) 'ID' from test order by id desc limit 1;

Requirement:
id as a primary key with auto-increment enabled

Table structure using above example:

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) NOT NULL auto_increment,
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Summary:
What this query does is set a local variable named previous_id, use "on duplicate key" to perform the insert or update and pass the action data (insert vs. update, which ID) via the select statement. Do not change "IF( (@previous_id := id) <> NULL IS NULL , VALUES (id), NULL )," other than the field name if you use something different. This example uses two fields, obviously use the same syntax and add on more as necessary.

This is all I could possibly ask for in a query - auto update/insert and a return value I can use to determine what happened and the ID. No issues with MySQL 4 returning wrong last insert ID or worrying about tables being changed running multiple queries in your code.

-jim