UPDATE 與 COALESCE 部分更新

這段 SQL 在做什麼？

UPDATE public.fleet_rv_models
   SET model_name        = $2,
       model_sku         = $3,
       model_description = $4,
       product_year      = $5,
       vin_model_code    = COALESCE($6, vin_model_code),
       wmi_code          = COALESCE($7, wmi_code)
 WHERE model_id          = $1;

語意可以拆成三層：

1. 定位：用 model_id = $1 找到要更新的那一筆車型
2. 整筆覆寫：model_name、model_sku、model_description、product_year 一律用傳入值覆蓋
3. 選擇性更新：vin_model_code、wmi_code 只有在有傳值（非 NULL）時才更新，沒傳就保留原值

常見情境：後台「編輯車型」API，基本欄位由表單整份送出；VIN 相關代碼則可能由其他流程維護，前端不一定會帶，不帶時不能被清空。

---

參數對照表

參數	對應欄位	更新方式	傳 NULL 的結果
$1	model_id	條件（不更新）	找不到任何列，更新 0 筆
$2	model_name	直接覆寫	欄位被設成 NULL（若有 NOT NULL 會報錯）
$3	model_sku	直接覆寫	同上
$4	model_description	直接覆寫	同上
$5	product_year	直接覆寫	同上
$6	vin_model_code	COALESCE 部分更新	保留原值
$7	wmi_code	COALESCE 部分更新	保留原值

---

COALESCE 是什麼？

COALESCE(a, b, ...) 會回傳參數中第一個不是 NULL 的值。

寫法	$6 的值	結果
COALESCE($6, vin_model_code)	'ABC'	'ABC'（更新）
COALESCE($6, vin_model_code)	NULL	原本的 vin_model_code（不變）
COALESCE($6, vin_model_code)	''（空字串）	''（會被更新成空字串）

重點：COALESCE 只認 NULL，空字串 ''、0 都不是 NULL，會被當成有效值寫進去。

---

用表格看差異

假設原本資料如下：

model_id	model_name	model_sku	product_year	vin_model_code	wmi_code
10	Cruiser	CR-01	2024	VMC01	1FT

情境 A：VIN 欄位都沒傳

參數：$1=10, $2='Cruiser X', $3='CR-02', $4='新版', $5=2025, $6=NULL, $7=NULL

model_id	model_name	model_sku	product_year	vin_model_code	wmi_code
10	Cruiser X	CR-02	2025	VMC01	1FT

基本欄位全部更新，vin_model_code、wmi_code 維持原值。

情境 B：只更新 wmi_code

參數：$6=NULL, $7='5YJ'（其餘同上）

vin_model_code	wmi_code
VMC01	5YJ

情境 C：前端傳了空字串

參數：$6=''

vin_model_code
（空字串）

原值被清掉了。若業務上「空字串也代表沒填」，需要改用 NULLIF（見下方）。

---

使用注意事項

1. 基本欄位必須整份傳入

$2～$5 沒有 COALESCE 保護，呼叫端一定要先讀出舊資料或帶齊所有欄位，否則漏傳的欄位會被設成 NULL。

2. 檢查影響筆數

model_id 不存在時，PostgreSQL 不會報錯，只會回 UPDATE 0。API 應該依影響筆數回傳 404：

影響筆數	意義	建議回應
1	更新成功	200 / 204
0	找不到該 model_id	404 Not Found

3. 空字串也視為「沒傳」：搭配 NULLIF

vin_model_code = COALESCE(NULLIF($6, ''), vin_model_code),
wmi_code       = COALESCE(NULLIF($7, ''), wmi_code)

NULLIF($6, '')：當 $6 等於 '' 時回傳 NULL，再交給 COALESCE 保留原值。

4. 這種寫法「無法主動清空」

用 COALESCE 之後，vin_model_code 永遠無法透過這支 SQL 被設成 NULL。若需要「清空」功能，要另外加旗標參數或拆成獨立 SQL：

vin_model_code = CASE
                   WHEN $8::boolean THEN NULL            -- 明確要求清空
                   ELSE COALESCE($6, vin_model_code)
                 END

5. 參數型別推斷

PostgreSQL 在 COALESCE($6, vin_model_code) 裡會用欄位型別推斷 $6，通常沒問題；若遇到 could not determine data type of parameter $6，可明確轉型：

vin_model_code = COALESCE($6::text, vin_model_code)

6. 想拿回更新後的資料：RETURNING

UPDATE public.fleet_rv_models
   SET ...
 WHERE model_id = $1
RETURNING model_id, model_name, vin_model_code, wmi_code;

省去再查一次 SELECT，沒有回傳列就代表找不到該筆資料。

---

程式呼叫範例

Python（asyncpg）

asyncpg 的佔位符同樣是 $1、$2…，SQL 可以直接沿用。

"""更新車型資料：VIN 相關欄位為選填，未提供時保留原值。"""

import asyncio
from dataclasses import dataclass

import asyncpg

UPDATE_RV_MODEL_SQL = """
UPDATE public.fleet_rv_models
   SET model_name        = $2,
       model_sku         = $3,
       model_description = $4,
       product_year      = $5,
       vin_model_code    = COALESCE(NULLIF($6, ''), vin_model_code),
       wmi_code          = COALESCE(NULLIF($7, ''), wmi_code)
 WHERE model_id          = $1
"""


class RvModelNotFoundError(Exception):
    """指定的 model_id 不存在。"""


@dataclass(slots=True, frozen=True)
class RvModelUpdate:
    model_id: int
    model_name: str
    model_sku: str
    model_description: str
    product_year: int
    vin_model_code: str | None = None  # None → 保留原值
    wmi_code: str | None = None  # None → 保留原值


async def update_rv_model(conn: asyncpg.Connection, data: RvModelUpdate) -> None:
    """執行更新，找不到資料時拋出 RvModelNotFoundError。"""
    status: str = await conn.execute(
        UPDATE_RV_MODEL_SQL,
        data.model_id,
        data.model_name,
        data.model_sku,
        data.model_description,
        data.product_year,
        data.vin_model_code,
        data.wmi_code,
    )

    # asyncpg 回傳像 "UPDATE 1" 的狀態字串
    match status.split():
        case ["UPDATE", "0"]:
            raise RvModelNotFoundError(f"model_id={data.model_id} not found")
        case ["UPDATE", _]:
            return
        case _:
            raise RuntimeError(f"unexpected status: {status}")


async def main() -> None:
    conn = await asyncpg.connect("postgresql://user:pass@localhost:5432/fleet")
    try:
        await update_rv_model(
            conn,
            RvModelUpdate(
                model_id=10,
                model_name="Cruiser X",
                model_sku="CR-02",
                model_description="新版",
                product_year=2025,
                wmi_code="5YJ",  # vin_model_code 不傳 → 保留原值
            ),
        )
        print("updated")
    except RvModelNotFoundError as exc:
        print(f"404: {exc}")
    finally:
        await conn.close()


if __name__ == "__main__":
    asyncio.run(main())

Go（pgx）

選填欄位用指標型別，nil 會被送成 NULL。

func UpdateRvModel(ctx context.Context, db *pgxpool.Pool, m RvModelUpdate) error {
	tag, err := db.Exec(ctx, updateRvModelSQL,
		m.ModelID,
		m.ModelName,
		m.ModelSKU,
		m.ModelDescription,
		m.ProductYear,
		m.VinModelCode, // *string，nil → 保留原值
		m.WmiCode,      // *string，nil → 保留原值
	)
	if err != nil {
		return fmt.Errorf("update rv model: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrRvModelNotFound
	}
	return nil
}

---

小結

• SET col = $n：一定覆寫，傳 NULL 就寫入 NULL。
• SET col = COALESCE($n, col)：有傳才更新，傳 NULL 保留原值。
• COALESCE 只認 NULL，空字串要搭配 NULLIF($n, '')。
• 用了 COALESCE 就無法透過同一支 SQL 清空欄位，需要時另加旗標。
• 記得檢查影響筆數（或用 RETURNING），區分「更新成功」與「找不到資料」。