insert and update in same sentence

itaym02

Greetings,

I have to pass info from table A to table B, I need for each record I passed (insert) into table b, to update the relevant record in table A.
Is there any way to do this only with sql i.e. mybee insert and update in the same sentence.

imaginary example:
insert into table_b (field_a,field_b)
select field_a, (update table_a

set field_b="processed"
where field_a=out.field_a)
from table_a out,

Sxooter

The nominal way to do this "atomically" is to use a transaction. If in MySQL you could use innodb tables.

In PostgreSQL or a cousin like Oracle, you could do it in a transaction, and the entry will disappear from one table and show up in the other. In PostgreSQL, should either statement fail, they would both be rolled back, and the user would see no change. Same for innodb tables in MySQL.

itaym02

Thanks,

Can there be transactions in mysql?

laserlight

Can there be transactions in mysql?

Read Transactions and Atomic Operations in the mysql manual.

itaym02

Thnaks

Did that.

I am using xampp to develop. I tried to follow the manual to activate the innoDB,
But it seems MySql in xampp is a bit different from what the manual knows.

Did anyone activated InnoDB in xampp?
(I will go to xamp site now)

Sxooter

Note that there are som mysql commands meant to get around this. There's a merge command and a few others I'm not that familiar with.

itaym02

thanks