平成30/ 2018-10-23 19:49
MYSQL/ UPDATE

-------------------------------------------------------------------------------------------------------------------
(LEFT OUTER など使いたいとき)
update
    contract_archive a,
    (
        select count(*) as ct from contract_archive
        where
            ym                 = '201604' and
            property_search_cd = '5026' and
            property_branch_cd = '00' and
            payee_cd           = '000001' and
            payee_branch_cd    = '00' and
            check_type_cd      = '105'
    ) b
set
    a.ym     = '201604'
where
    a.ym                 = '201603' and
    a.property_search_cd = '5026' and
    a.property_branch_cd = '00' and
    a.payee_cd           = '000001' and
    a.payee_branch_cd    = '00' and
    a.check_type_cd      = '105' and
    b.ct = 0
;


----------
複数 例
----------
update
    recruit_temporary a,
    recruit           b
set
    a.payment_comment = b.payment_comment
where
    a.id = 2214 and b.id = 94619
;



| 例1
|----------
update TwitterUser a,
(
select aa.from_user_id, aa.from_user, aa.from_user_name from Twitters aa inner join
    (select max(id) as id, from_user_id from Twitters group by from_user_id) bb on aa.id = bb.id
group by aa.from_user_id
) b
set a.user = b.from_user, a.user_name = b.from_user_name
where a.user_id = b.from_user_id
;




|
| mysql/ update plural 複数
|----------
update
    acct, acct_item1
set
    acct.item_name = acct_item1.name
where
    acct.acct_item1_id = acct_item1.id
;


update zipcode a, dev.zipcode b
set
    a.city_lat=b.city_lat,
    a.city_lng=b.city_lng,
    a.block_lat=b.block_lat,
    a.block_lng=b.block_lng,
    a.modified=b.modified
where
    a.zipcode=b.zipcode and
    a.city_name=b.city_name and
    a.block_name=b.block_name
;





( Thanks. )
複数のテーブルに対するUPDATE
http://rfs.jp/sb/sql/03/05.html
UPDATE goods, goods_new
    SET items.price=goods_new.price
    WHERE goods.id_goods=goods_new.id_goods;


//-----別名つけられる
$sql = "update tours a, tours b set a.sche_text = b.sche_text
where a.id = {$newid} and b.id = {$id}";
mysql_query($sql);
//-----//