Replace Statement

jazappi

I can't figure out why this replace stament is throwing a error, can somebody look at the syntax and tell me what I am doing wrong?

$sql = "REPLACE INTO customer_servers 
 SET company_id='$company_id',server_type_id='1',server_shortname='$value', server_ip='$value2', server_fqdn='$server_fqdn' 
		WHERE cs_server_id='$fs_cs_server_id'"; 
		mysql_query($sql) or die (mysql_error());

jonno946

Originally posted by jazappi
I can't figure out why this replace stament is throwing a error, can somebody look at the syntax and tell me what I am doing wrong?
$sql = "REPLACE INTO customer_servers 
 SET company_id='$company_id',server_type_id='1',server_shortname='$value', server_ip='$value2', server_fqdn='$server_fqdn' 
		WHERE cs_server_id='$fs_cs_server_id'"; 
		mysql_query($sql) or die (mysql_error());
[/B]

i may be wrong (i've never heard/used the replace syntax) but can you not just use update instead:

$sql = "UPDATE customer_servers 
 SET company_id='$company_id',server_type_id='1',server_shortname='$value', server_ip='$value2', server_fqdn='$server_fqdn' 
		WHERE cs_server_id='$fs_cs_server_id'"; 
		mysql_query($sql) or die (mysql_error());

😕

jazappi

I am replacing a unique/index field which according to mysql I need to use the replace instead of update, if anybody knows diffrent please let me know.

nemonoman

REPLACE isn't legal on all versions of MySQL. I forget when it became a standard; I think 4.1 something. The MySQL website, as usual, is less than useless.

Also, REPLACE has a lot of messiness with INNODB type tables...you're not using INNODB type tables, are you? If so, forget replace, and read up on constraints.

HOWEVER...Why use REPLACE at all?

The action of REPLACE is to combine
DELETE/INSERT statements:

DELETE FROM sometable WHERE id=1
INSERT INTO sometable SET id=1

You can do the same with 2 statements: DELETE then INSERT.

If you know the row exists, UPDATE will work OK, as the earlier poster suggested.

I think you have misread the manual about REPLACE being required. However, you get my vote for having read the manual at all.

Roger_Ramjet

It would help to know what is wrong with the query if we could see the error generated.

Repace is the correct statement to use if you do not know whether the key already exists = Insert if not present, otherwise update the existing row.
You can also do the same thing with the 'Insert .... On Duplicate Key Update' syntax of the Insert statement. But I think Replace is clearer and easier to use.

Only problem with either is that they are new so earlier versions of mysql do not recognise or support them.

robert_w_ark

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.