MYSQLでSELECTを利用する

条件を分岐した更新と新規作成

caseを利用した処理

複数テーブルに更新をする場合の処理は、どうやらcase式で書いていける感じです。

■caseで書いた場合

CASE
-- 新規登録
WHEN NOT EXISTS

(SELECT company_id FROM company_t WHERE company_id = ?)

THEN
INSERT INTO

company_t (company_id, company_name)

VALUES (?, ?)

INSERT INTO school_t (company_id, school_id, school_name) VALUES (?, ?, ?)

-- 既存の会社で、新しい塾を登録する場合
WHEN NOT EXISTS

(SELECT school_id FROM school_t

WHERE school_id = ?)

THEN
INSERT INTO school_t (company_id, school_id, school_name) VALUES (?, ?, ?)

-- 既存の会社で、既存の塾を更新する場合
WHEN EXISTS

(SELECT * FROM school_t

WHERE company_id = ? AND school_id = ?)

THEN
UPDATE school_t SET school_name = ?

WHERE company_id = ? AND school_id = ?

-- 既存の会社で、新しい塾を追加する場合
ELSE
INSERT INTO school_t (company_id, school_id, school_name)

VALUES (?, ?, ?)
END;

■switch

switch ($action) {
case 'new_company':
if (!exists_company($company_id)) {
insert_company($company_id, $company_name);
insert_school($company_id, $school_id, $school_name);
}
break;
case 'new_school':
if (!exists_school($school_id)) {
insert_school($company_id, $school_id, $school_name);
}
break;
case 'update_school':
if (exists_school($company_id, $school_id)) {
update_school($company_id, $school_id, $school_name);
} else {
insert_school($company_id, $school_id, $school_name);
}
break;
default:
break;
}