//FMDB 根据字段更新数据库,同字段多个值只更新一条

        [queue inDatabase:^(FMDatabase *db) {

            BOOL isSucceedQty =  [db 
executeUpdate:[NSString 
stringWithFormat:@"update %@ set CurrentQty = ? where Item = ? limit 1",tableString],currentQtyStr,itemStr];

          }

        }];

}




//FMDB 



//FMDB 




//FMDB 



//FMDB 

1.更新单个字段

update  a  set a.title='更新值'

2.更新多个字段

update a  set a.title='title更新值',a.createId='createId更新值'

3.从A表查出多个字段更新B表多个字段

 update  a set( a.user_id,a.name)=

    (SELECT listagg(bb .USER_ID,',') WITHIN GROUP (ORDER BY bb .USER_ID) ,

    listagg(bb .USER_NAME,',') WITHIN GROUP (ORDER BY bb .USER_NAME) 

    from bb  WHERE bb .aID ='*****' 

    GROUP BY  bb .id) where a.id='*****'

说明a表为主表，bb表为从表 

 

开发过程中难免碰到修改数据的trouble，怎么快速更新多条数据？以下是postgresql实操：
1、需要更新同一字段值的多条数据
update pf_model_info 
set update_time = '20200528131400'
where model_id in ('83085dbda97b45699469c46c623d0258','a4b0038554bc4d1bb12a7bfb97ca01ab','1373ca9d87564b8c9a65151da8ebf13d') 
-- 所要更新字段的主键值


2、需要更新一个字段不同值的多条数据
update pf_model_info test set model_img_url = tmp.model_img_url 
from
(values
('9914b90cf7324570a4ac8f59fe335a7e',  '12'),
('df71d00abba54260a421d2f5e289d557',  '13'),
('90311d8fbb20411cb440b00abf04e5a6',	'15')
) 
as tmp (model_id,model_img_url) where test.model_id=tmp.model_id; 



延伸扩展

这样就可以操作多个字段不同值多条数据的修改了 YES!

update pf_model_info test set model_img_url = tmp.model_img_url,model_name = tmp.model_name
from
(values
('9914b90cf7324570a4ac8f59fe335a7e',  '12', '测试模块1'),
('df71d00abba54260a421d2f5e289d557',  '13', '测试模块2'),
('90311d8fbb20411cb440b00abf04e5a6',	'15', '测试模块3')
) 
as tmp (model_id,model_img_url,model_name) where test.model_id=tmp.model_id; 

今天做项目需求发现一个比较好用的单条SQL语句mysql批量更新数据库.
CASE  WHEN  THEN  END
//还原城市原有排序
$ids = '';
$sql = "UPDATE ffan_cities SET asort = CASE cityId ";
foreach ($showCities as $k => $v) {
$sql .= sprintf("WHEN %d THEN %d ", $v['cityId'], $v['asort']);
$ids .= $v['cityId'].',';
}
$ids = trim($ids,',');
$sql .= "END WHERE cityId IN ($ids)";
$res = $this->execute($sql);
看上面代码,最后一句代码执行SQL语句,前面都是组织好SQL语句准备的.最得出这样一条SQL:
UPDATE ffan_cities
SET asort = CASE cityId
WHEN 11023 THEN 3
WHEN 11024 THEN 7
END
WHERE cityId IN (11023,11024)
这条SQL很好理解,条件当cityId=11023时 asort更新为3,后面的类推,最后where条件 cityId IN 这个组里边为了提高执行效率.
还有个可以批量更新多个字段的语句,大家可以看看.(网上找的例子,其实都差不多)
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)