在cakephp / sql服务器中获取更新记录的PK [英] Getting PK of updated records in cakephp/sql server

问题描述

我已使用批量更新在两个表之间同步数据，如下所示

I have used bulk update to sync datas between two tables as below

$sqlProc="
UPDATE cards
SET cards.card_no = t2.card_number,
    cards.expiry_date=t2.expiry_date OUTPUT INSERTED.Id AS 'updated_id'
FROM cards
INNER JOIN card_temp t2 ON (cards.account_no = t2.account_number
                            AND cards.customer_name=t2.customer_name)
WHERE cards.is_disabled='N'";
        debug($this->Request->query($sqlProc));

上述查询也将使用 OUTPUT INSERTED.Id返回更新记录的主键AS'updated_id'在sql server编辑器，但是当我调试sql

Above query will also return Primary key of updated records usingOUTPUT INSERTED.Id AS 'updated_id' in sql server editor but when i debug the sql

debug($this->Request->query($sqlProc));

然后返回 true false 用于查询失败。

Then it return true for successful query and false for unsuccessful query.

有任何想法来获取 updated_id 到数组，以便我可以使用这些ids到另一个表

Is there any idea to fetch updated_id to array , so that i can use those ids to another table

推荐答案

好吧，我想出来了。我创建了一个中间表，并执行了以下操作：

Well , I figured out finally . I created one intermediate table did the following

//inserted into temp_id (bridge table)
$sqlProc="insert into temp_id select * FROM (update cards 
                        set         cards.card_no = t2.card_number,cards.expiry_date=t2.expiry_date
                        OUTPUT INSERTED.Id AS 'updated_id'
                        from        cards 
                        inner join  card_temp t2
                        on          (cards.account_no = t2.account_number and cards.customer_name=t2.customer_name)
                        where cards.is_disabled='N'
                        ) AS t";
        $this->Request->query($sqlProc);
        //fetch from intermediate table

        $sqlSel="SELECT * FROM temp_id";
        $arr=$this->Request->query($sqlSel);
          //this array will fetch all updated id's
        debug($arr);
        $sqlDel="DELETE FROM temp_id";
        $this->Request->query($sqlDel);

这篇关于在cakephp / sql服务器中获取更新记录的PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！