trigger to update the same table different row

[TomoT]

Hi,
i have table:

id | data
1 | aa
2 | bb
1 | cc

If i will update data in first row to 1 - abc
i need to copy this value to 1 | cc
so after update table should looks like this:
1 | abc
2 | bb
1 | abc

So i tried trigger, but there is an error
#1442 - Can't update table 'example' in stored function/trigger
because it is already used by statement which invoked this stored
function/trigger.

So i tried to create trigger on table view, but... you can't do that
either.

So any advice ? different approach ?
Regards,
Tom

[Erick T. Barkhuis]

TomoT:

>Hi,

Hello

>i have table:
>
>id | data
>1 | aa
>2 | bb
>1 | cc

Is 'id' really an identifying field, or just something called 'id'?

>If i will update data in first row to 1 - abc
>i need to copy this value to 1 | cc
>so after update table should looks like this:
>1 | abc
>2 | bb
>1 | abc

Does this example show that you want to update all rows where id==1 and
set field 'data' to value abc?


>So i tried trigger,

Wouldn't do

UPDATE yourtable
SET data = "abc"
WHERE id = 1

exactly what you want? Or do I misinterprete your (limited) example?


--
Erick

[Tony Mountifield]

Hi Tom,

In article <02e31b62-5476-4d12...@z19g2000vbe.googlegroups.com>,

I think you need to explain what you are trying to achieve. It seems like
you have posted your supposed solution, i.e. using a trigger, which you
have found doesn't work, but not the problem it is attempting to solve.

What does the data mean? In your "after" table, you have two duplicate rows.

It may be that you need to rethink the data model so that there is no
need to update other rows in the same table as an original row, which
as you say, MySQL doesn't seem to allow.

Cheers
Tony
--
Tony Mountifield
Work: to...@softins.co.uk - http://www.softins.co.uk
Play: to...@mountifield.org - http://tony.mountifield.org

[Jerry Stuckle]

Two rows with the same id? Not a good design; each row should have a
unique id (primary key).

But to answer your question, RDBMS's won't allow you to update the same
table in a trigger. This could, for instance, lead to a loop updating
rows (Update to row 1 triggers an update to row 2 which triggers an
update to row 3 which triggers an update to row 1...).

Additionally, the same value in multiple rows which must be kept in sync
is a violation of normalization rules and can cause all kinds of
problems (i.e. LOAD TABLE doesn't run triggers).

The correct solution is a second table containing the value; a column in
the first table has a foreign key to the second one. Both row 1's in
this case would point at the same row in the second table, so when the
second table is updated, both rows in the first table contain the same
information.

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

[TomoT]

yes, my example isn't clear...so i will specify.
id - isn't unique.This is only a part of table.
I just need to update(keep in sync) every row where id=1
sync must be done automatically after any of rows with id=1 will be
changed

So Jerry if i'm unable to build relation table, because application
only updates this table and i'm restricted to data in this table, i
can't achieve what i wants ?
And the only way is to modify application ?

[Jerry Stuckle]

The correct way, as I said, would be to redesign your tables. You are
just asking for trouble the way you have it.

Otherwise, the only way to do it would be to force the application to
update all the rows where id=1. But then you need to ensure all
applications do it every time (and you never do a LOAD TABLE or similar
with incorrect data).

Of course, you could also use a stored procedure to do the update, but
then you'd just have to ensure every application calls the stored
procedure instead of just updating the table. Not much difference here
(except slightly slower performance).

[IRC]

Also, I suggest you to restructure and optimize db tables and try to
solve it with normal query. And, if you can not able to do that way,
then its better to write the stored procedure.

> jstuck...@attglobal.net
> ==================