複数テーブルへのupdate+insert　まとめ

複数テーブルへのupdate+insert

【やりたい事】

複数テーブルにまたがって外部キー制約されているカラムに、upsert処理をしたい。

カラムにデータがあれば、update

データがなければinsert

をしていきたい。

■結論

①joinをする

②外部キー+CASCADE制約+replace

【考察】

joinして書いた方が、レベルが上がりそう！

②はすでにやって、かなりカンタンだったが、複合主キーなどに、少し難がある

1.【考察】まずは整理

2.【結合】JOINを利用

3.【制約】ON UPDATE CASCADE

4.【連結】JOINしてUPDATEしてみよう

5.【join】2つのテーブルをjoin

6.【複数】3つのテーブルをjoin

1.リレーションシップ関係にあるデータの更新

■やりたい事

・複数テーブルにまたがってUPDATEを行う

■検索キーワード

・mysql 複数テーブル更新

■手法は2つ

①外部キーで関連付けられた別のテーブルのデータを更新

②MySQL 複数テーブルを JOIN してデータを更新 (UPDATE)>>

※JOINの注意点

複数テーブル更新クエリでは、条件を満たす各レコードが更新されます。

基準が複数回一致した場合でも、行は 1 回だけ更新されます。複数のテーブルを更新する構文は、ORDER BY および LIMIT キーワードでは使用できません。

Q,2つの方法があるけれど、どうしたらいいのか？？

JOINとcascade+replaceのどちらを利用するのか？？

2. JOINを利用

JOINを使用する方法

この方法では、外部キーで結合されたテーブルのデータをJOINし、更新するデータを含む新しい値を設定し、UPDATEクエリを実行します。

CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_price DECIMAL(10, 2)
);

CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50)
);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE;

ここで、ordersテーブルのcustomer_id列がcustomersテーブルのid列と外部キーで結合されています。

ordersテーブルのある行のcustomer_idを更新するには、以下のようなSQLクエリを実行します。

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.customer_id = 2, customers.email = 'new_email@example.com'
WHERE orders.id = 1;

この例では、ordersテーブルのcustomer_idが2に変更され、customersテーブルの対応する行の「email」が更新されます。

3. ON UPDATE CASCADE

外部キー制約のON UPDATE CASCADEを使用する方法

外部キー制約を設定して、ON UPDATE CASCADEオプションを使用することで、関連するテーブルのすべての行が自動的に更新されます。

例えば、以下のようなordersテーブルがあるとします。

CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_price DECIMAL(10, 2),
CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

ここで、customer_id列に外部キー制約が設定され、ON UPDATE CASCADEオプションが使用されています。

これにより、customersテーブルのid列が更新されると、ordersテーブルのcustomer_id列も自動的に更新されます。

ordersテーブルのある行のcustomer_idを更新するには、以下のようなSQLクエリを実行します。

UPDATE customers SET id = 2 WHERE id = 1;

この例では、customersテーブルのidが1の行がidが2に更新され、ordersテーブルのすべての行のcustomer_idが自動的に更新されます。

4.joinをやってみよう

1.SELECE文を書く

2.UPDATE文を書く

1.SELECE文を書く

SELECT * FROM exam_results AS er

INNER JOIN exams AS e ON er.exam_id = e.exam_id

INNER JOIN students AS s ON er.student_id = s.student_id

WHERE e.exam_name_jp = '数学１'

AND s.gender = 'M';

join文の参照>>

ASってここで使うのか。短く書く事ができるようだけど、なんだか逆に一見性がないような気がする＾＾；

自分しか分からないようなASは書かない方が無難かも。

2.UPDATE文を書く

UPDATE exam_results AS er

INNER JOIN exams AS e ON er.exam_id = e.exam_id

INNER JOIN students AS s ON er.student_id = s.student_id

SET er.score = er.score + 1

WHERE e.exam_name_jp = '数学１'

AND s.gender = 'M';

SET部分が増えている。insertなんで、何を入れるのか？を指定したわけです。

これで文章が完成。

5.2つのテーブルをjoin

■JOINの文章について

SELECT * FROM monthly_sales

JOIN employee_name_entry_year

ON monthly_sales.id = employee_name_entry_year.id;

monthly_salesテーブルを基準にして結合をしていきます。

■説明

SELECT テーブル1から抽出するカラム FROM 基準にしたいテーブル1の名前

JOIN 結合させたいテーブル2の名前

ON テーブル1の名前.基準にするカラム名 = テーブル2の名前.基準にするカラム名;

■ASをつける

SELECT * FROM monthly_sales AS x

JOIN employee_name_entry_year AS y

ON x.id = y.id;

ON以下を書くのがすごい楽になる。

■USINGを使う

SELECT * FROM monthly_sales

JOIN employee_name_entry_year

USING(id);

ON部分をさらにすっきり掛けます。

※ONにはカラム名が異なっても中身が同じであれば問題ない、という仕様があります

■構文

SELECT x.id, x.sales AS 売上, y.name AS 名前, y.entry_year AS 入社年, z.prefecture AS 出身県 FROM monthly_sales AS x

JOIN employee_name_entry_year AS y

USING(id)

JOIN employee_address AS z

USING(id);

こんな感じになるらしいです。

【参照】

参照はこちらです＞＞

6.3つのテーブルをjoin

SELECT * FROM テーブル1

JOIN テーブル2 ON テーブル1.user_id = テーブル2.user_id

JOIN テーブル3 ON テーブル1.user_id = テーブル3.user_id

WHERE テーブル1.user_id = 値

// 結果 user_id,氏名,性別,年齢,生年月日

JOINでは2つ以上のテーブルを扱う都合上

カラム名がどのテーブル由来のものかを明示するために「テーブル名.カラム名」という指定の仕方をします。

SELECT *

FROM テーブル1.*,テーブル2.* ,テーブル3.年齢

JOIN テーブル2 ON テーブル1.user_id = テーブル2.id

JOIN テーブル3 ON テーブル1.user_id = テーブル3.id

WHERE テーブル1.user_id = 値

// 結果 user_id,氏名,性別,年齢

参照はこちら>>

$query = $this->db->query("SELECT * FROM company_t
JOIN company_name_t ON company_t.company_id=company_name_t.company_id
JOIN school_t ON company_t.company_id=school_t.company_id
WHERE company_t.company_id='$post_data_company'");