数据库:如何用一个更新查询更新多个表

I saw this on the codeigniter forum

我在codeigniter论坛上看到过

Considering the below code

考虑下面的代码

UPDATE a
INNER JOIN b USING (id)
SET a.firstname='Pekka', a.lastname='Kuronen',
b.companyname='Suomi Oy',b.companyaddress='Mannerheimtie 123, Helsinki Suomi'
WHERE a.id=1;

This is how you would apparently do it in Codeigniter

这就是你在可待因点火器中所做的

$this->db->set('a.firstname', 'Pekka');
$this->db->set('a.lastname', 'Kuronen');
$this->db->set('b.companyname', 'Suomi Oy');
$this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi');
$this->db->where('a.id', 1);
$this->db->join('table2 as b', 'a.id = b.id');
$this->db->update('table as a');

this does not work in reality. I have had a look a the SQL which this produces and the results do not even mention the join.

这在现实中行不通。我查看了它生成的SQL，结果甚至没有提到join。

Does anyone have any idea how to do an update with a join using Codeigniter's Active Record Database Class?

有人知道如何使用Codeigniter的活动记录数据库类对连接进行更新吗?

2 个解决方案

#1

One solution I have found is to remove the join altogether and move the join condition into a 'where' function, also you will need to change the update string to include the new table.

我发现的一种解决方案是完全删除连接并将连接条件移动到“where”函数中，还需要更改更新字符串以包含新表。

$this->db->set('a.firstname', 'Pekka');
$this->db->set('a.lastname', 'Kuronen');
$this->db->set('b.companyname', 'Suomi Oy');
$this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi');

$this->db->where('a.id', 1);
$this->db->where('a.id = b.id');
$this->db->update('table as a, table2 as b');

#2

Using two separate queries within a transaction should solve your problem. If a query fails the other one gets rolled back.

在事务中使用两个独立的查询应该可以解决您的问题。如果查询失败，则回滚另一个查询。

#1