자주 쓰는 Mysql 쿼리문
- 특정 문자 체해서 업데이트
UPDATE write_clean SET wr_content = REPLACE(_content, '\\', '') where id = '1';
UPDATE write_clean SET wr_content = REPLACE(_content, '/userfiles', '/data/files/userfiles') where id = '1';
- select 한 다음에 update
$aaa = " UPDATE admin_member
SET member_level = CASE
WHEN member_level = 0 THEN 1 /** member_level이 0 이었을 때 1로 업데이트 **/
ELSE member_level
END where member_id = 'seitahyi14';
// 레벨이 2인 일반회원을 조회한 후 레벨 업
$LvSql1 = "update g5_member set mb_level = 5
where mb_level = 2
and mb_id IN
(
select mb_id from
(
SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
FROM g5_shop_order
WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
group by mb_id having price >= ".$default['level_up_price']."
) t
)";
// 레벨이 5이고, 한해 200만원의 결제 총액이 아닌 일반회원을 조회한 후 레벨 다운
$LvSql2 = "update g5_member set mb_level = 2
where mb_level = 5
and mb_id IN
(
select mb_id from
(
SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
FROM g5_shop_order
WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
group by mb_id having price < ".$default['level_up_price']."
) t
)";
// 레벨이 2인 기업회원을 조회한 후 레벨 업
$LvSql1 = "update g5_member set mb_level = 6
where mb_level = 4
and mb_id IN
(
select mb_id from
(
SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
FROM g5_shop_order
WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
group by mb_id having price >= ".$default['level_up_price']."
) t
)";
// 레벨이 5이고, 한해 200만원의 결제 총액이 아닌 일반회원을 조회한 후 레벨 다운
$LvSql2 = "update g5_member set mb_level = 4
where mb_level = 6
and mb_id IN
(
select mb_id from
(
SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
FROM g5_shop_order
WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
group by mb_id having price < ".$default['level_up_price']."
) t
)";
다중 문자열 치환 -
select code,
case
when code = '0'
then '사료'
when code = '1'
then '간식'
when code = '2'
then '영양제'
when code = '3'
then '용품'
else 'ETC'
end as 상품카테고리,
from table;
출처: https://thalals.tistory.com/339 [힘차게, 열심히 공대생:티스토리]
select 조회 후 insert -