設想
[email protected]需要在表 2 中具有與[email protected].
目前[email protected]有user_profile50 個，而不是user_profile10 個和 20 個。
描述
我想創建一個進入 Table_2 的查詢，與當前的id_profiles查詢相同。user_email [email protected][email protected]
我們擁有的唯一知識是user_email用戶和表/列名。
表格1
| id_user | 用戶電子郵件 |
|---|---|
| 1 | [email protected] |
| 2 | 用戶@website.com |
表 2
| id_user | 用戶資料 |
|---|---|
| 1 | 10 |
| 1 | 20 |
| 2 | 50 |
表2（預期資料）：
| id_user | 用戶資料 |
|---|---|
| 1 | 10 |
| 1 | 20 |
| 2 | 10 |
| 2 | 20 |
下面的查詢沒有按預期作業，因為它僅在 Table_2 中沒有 user_id '2' 的行時插入資料。
DECLARE @adminEmail nvarchar(255) = '[email protected]'
DECLARE @userEmail nvarchar(255) = '[email protected]'
DECLARE @adminEmailID int = (SELECT id_user FROM Table_1
WHERE user_profile = @adminEmail);
INSERT INTO Table_2 (id_user, user_profile)
SELECT
(SELECT id_user FROM Table_1
WHERE user_email = @userEmail) AS id_user,
b.user_profile
FROM
Table_2 b
INNER JOIN
Table_1 a ON a.id_user = b.id_user
WHERE
NOT EXISTS (SELECT * FROM Table_2
WHERE id_user = (SELECT id_user FROM Table_1
WHERE user_email = @userEmail))
uj5u.com熱心網友回復：
你可以用MERGE這個。
請注意以下事項：
- 您必須使用 CTE、視圖或派生表預先過濾源表和目標表，否則您最終會洗掉整個表。不要陷入將所有條件都放在
ON. - 您需要找到目標用戶的 ID，以便針對該 ID 進行插入。您不能在 中執行此操作，
MERGE因為由于JOIN.
DECLARE @adminEmail nvarchar(255) = '[email protected]';
DECLARE @userEmail nvarchar(255) = '[email protected]';
DECLARE @userEmailID int = (SELECT id_user FROM Table_1
WHERE user_email = @userEmail);
WITH Source AS (
SELECT t2.*
FROM Table_2 t2
JOIN Table_1 t1 ON t1.id_user = t2.id_user
WHERE t1.user_email = @adminEmail
),
Target AS (
SELECT t2.*
FROM Table_2 t2
WHERE t2.id_user = @userEmailID
)
MERGE Target t
USING Source s ON s.user_profile = t.user_profile
WHEN NOT MATCHED BY TARGET THEN
INSERT (id_user, user_profile)
VALUES (@userEmailID, s.user_profile)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;
db<>小提琴
- 根據連接的復雜性，將其作為單獨的
INSERTandDELETE陳述句執行可能更容易且性能更高
DECLARE @adminEmail nvarchar(255) = '[email protected]';
DECLARE @userEmail nvarchar(255) = '[email protected]';
INSERT Table_2 (id_user, user_profile)
SELECT t1User.id_user, t2.user_profile
FROM Table_2 t2
JOIN Table_1 t1Admin ON t1Admin.id_user = t2.id_user
AND t1Admin.user_email = @adminEmail
JOIN Table_1 t1User ON t1User.user_email = @userEmail
WHERE NOT EXISTS (SELECT 1
FROM Table_2 t2Existing
WHERE t2Existing.id_user = t1User.id_user
AND t2Existing.user_profile = t2.user_profile);
DELETE t2
FROM Table_2 t2
JOIN Table_1 t1User ON t1User.id_user = t2.id_user
AND t1User.user_email = @userEmail
WHERE NOT EXISTS (SELECT 1
FROM Table_2 t2Admin
JOIN Table_1 t1Admin ON t1Admin.id_user = t2Admin.id_user
WHERE t1Admin.user_email = @adminEmail
AND t2Admin.user_profile = t2.user_profile);
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425369.html
上一篇：STRING_AGG與全部聯合
下一篇：為具有指定值的行分配連續ID