using REPLACE command error..

[nag]

Hi,

I am trying to REPLACE few fields(entire data of the field) of a table with other table fields. The key is id in both the tables. The sql query is..

REPLACE INTO
ftable (fc1,fyc1,bc1,rc1)
SELECT
fc,fyc,bc,rc
FROM
stable WHERE ftable.id = stable.agnt;

It is giving error as "unknown column in where clause.

Is there any other way to do it?

Thanking you.

[Jerry Stuckle]

If you're updating a few fields, you should be using the UPDATE command.
REPLACE will delete the entire row before inserting the new data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Jerry Stuckle]

On 4/5/2013 10:57 AM, nag wrote:

I should add - if you DO try to replace the entire field, the DELETE
could have unforeseen consequences - for instance, if you have another
table that specifies a foreign key with ON DELETE CASCADE (and are using
InnoDB), the DELETE in the table will delete the applicable row(s) in
the other table, also. This can cause real problems, especially if you
define the foreign key at a later time.

[nag]

UPDATE worked, but I have updated one field at a time.. like

UPDATE ftable f1 , stable s1
SET f1.fc1 = s1.fc
where f1.id = s1.id;

like 4 fields 4 times.

[Jerry Stuckle]

No, you can update multiple columns (not fields) in the same UPDATE
statement.

[nag]

> > where f1.id = s1.id;email address

>
> Jerry Stuckle
>
> JDS Computer Training Corp.
>
> jstu...@attglobal.net
>
> ==================

yes.

UPDATE ftable f1 , stable s1

SET

f1.fc1 = s1.fc,
f1.fyc1 = s1.fyc,
f1.rc1 = s1.rc,
f1.bc1 = s1.bc

where f1.id = s1.id;

thank you.