REPLACE when no record

quanghoc

I have table 'table1' with fields:
+ field1: primary
+ field2
+ field3
I want to run a SQL statement like this
REPLACE INTO table1 SET field1='something1', field2='something2', field3='something3';
However, on 'field3', I only want to replace it with 'something3' only if there is no old record exist. Otherwise, don't replace field3='something3'. How to do this?

hand

Originally posted by quanghoc
... it with 'something3' only if there is no old record exist. Otherwise, ...

😕

UPDATE table1 SET field1="content1" WHERE ....

quanghoc

well, UPDATE is something else. Because I still want to create a new record if none exist!

Lars_Berg

Then you have to check if it exists first

i.e.

$q = "select 1 from table1 where field1 = " . $key;
$r = mysql_query($q);
if ( mysql_num_rows($r)) == 0) {
  // insert
} else {
  //update
}

quanghoc

Is there anyway to avoid having more than one statement?
Something like this:
replace into table1 set f1=2, f2=IF(f2=null,111,f2),f3=13;
But I don't know why this doesn't work. It set f2=0 to an exist record instead of keeping f2 as the same!!

siwis

You said you wanted to insert new data as well. By this do your mean that you want to insert new rows????

If not, and it is only field3 that you want to populate (if it is empty) then I'd try:

UPDATE table1 SET field3='something3' WHERE field3 = ''

quanghoc

Yes, I want to insert new as well if there is none exist.

siwis

I really don't understand exactly what you want.

You are saying (as I read it) that you not only want to update existing records but to insert new records as part of one process?????????

But that simply isn't possible.

Now if all you want to do is to update field3 if it is empty then you need to do the sql I gave above.
If you want to replace contents of fields 1 & 2 but only replace the contents of field 3 if it is empty then I don't think you are going to be able to do this with one query, it will take 2 queries, one to replace field1&2 in ALL records, and a second to replace field3 if it is empty (ie WHERE field3='')

Until you can make yourself a little clearer as to what you want to do, its pretty hard to offer any real advice.

quanghoc

OK, just like this:
+ Find field1 if there is a record, if so leave field3 alone and update field2. If not, insert a new record with all fields.

[deleted]

You'll have to do that in two queries.

The 'replace' command is tricky as it will literally replace, taking out all the old data before inserting the new data, this is very different from an update.

You could just do the update query, then check wether any records were affected (mysql_affected_rows()) and if none were affected, you can do the insert-query.

But take care, this is not multi-user safe without transactions!

siwis

Just look at this pseudo code and tell me if I am correctly understanding your needs:

Query Database for record
if -
field1 of record is not empty, update field3, leave field2 alone
else if field1 IS empty -
enter completely new row with fields 1,2&3 (dunno what data)
leave orginal record untouched.

is that correct??????

quanghoc

Nope, goes like this:
If not exist a record with field1='something1'
insert a new record with field1,2,3
else
update field3
But look like I have to go with the solution of 'vincente'!! Is there any other database system that can do this? MySQL so far seems to have many limitation to me especially about Unicode and Full text search.

Originally posted by siwis
Just look at this pseudo code and tell me if I am correctly understanding your needs:

Query Database for record
if -
field1 of record is not empty, update field3, leave field2 alone
else if field1 IS empty -
enter completely new row with fields 1,2&3 (dunno what data)
leave orginal record untouched.

is that correct??????

[deleted]

Database like PostgreSQL support stored procedures, functions and triggers, which effectively let you do some scripting inside the query.

That would solve it, but the PHP solution is simpler. Not better, but simpler.

quanghoc

Will Oracle & SQL Server solved the problem too? Because when it comes to develop a larger application, MySQL seems to have too many restriction

[deleted]

Oracle and MS-SQLserver can do allmost anything. (and so they should for the bizar amount of cash they cost)