通过中间表更新News表数据的SQL实现问题求助

首先明确：你的需求不是单纯更新现有行，而是要为每个id_title对应的多条Detail记录新增行（仅填充paragraph，其余字段为空），同时更新原行的content和第一个paragraph。原UPDATE语句只能修改现有行，无法实现新增行，且存在字段名错误（detail.title应为detail.id_title）。

一、修改News表（插入新行以匹配期望结果） #

步骤1：更新原News行的content字段（关联Title表） #

UPDATE news
SET content = title.content
FROM title
WHERE news.id_title = title.id_title;

步骤2：更新原News行的paragraph为对应id_title的第一条Detail记录 #

UPDATE news n
SET paragraph = d.paragraph
FROM (
    -- 给每个id_title的Detail按id_detail排序，标记第一条
    SELECT id_title, paragraph,
           ROW_NUMBER() OVER (PARTITION BY id_title ORDER BY id_detail) AS rn
    FROM detail
) d
WHERE n.id_title = d.id_title AND d.rn = 1;

步骤3：插入剩余Detail对应的空行（仅填充paragraph） #

INSERT INTO news (paragraph)
SELECT d.paragraph
FROM (
    SELECT id_title, paragraph,
           ROW_NUMBER() OVER (PARTITION BY id_title ORDER BY id_detail) AS rn
    FROM detail
) d
JOIN news n ON d.id_title = n.id_title
WHERE d.rn > 1;

执行以上三步后，News表的数据将与你期望的结果一致。

二、仅查询得到期望结果（不修改原表） #

如果不需要修改News表，只是想查询出目标格式的结果，可以用以下SQL：

SELECT
    CASE WHEN d.rn = 1 THEN n.number END AS number,
    CASE WHEN d.rn = 1 THEN n.date END AS date,
    CASE WHEN d.rn = 1 THEN n.id_title END AS id_title,
    CASE WHEN d.rn = 1 THEN t.content END AS content,
    d.paragraph
FROM news n
LEFT JOIN title t ON n.id_title = t.id_title
LEFT JOIN (
    SELECT id_title, paragraph,
           ROW_NUMBER() OVER (PARTITION BY id_title ORDER BY id_detail) AS rn
    FROM detail
) d ON n.id_title = d.id_title
-- 补充没有对应Detail的News行
UNION ALL
SELECT n.number, n.date, n.id_title, t.content, n.paragraph
FROM news n
LEFT JOIN title t ON n.id_title = t.id_title
WHERE n.id_title IS NULL
-- 按原number排序，同number的paragraph按顺序排列
ORDER BY 
    COALESCE(n.number, 9999),
    d.rn;

内容的提问来源于stack exchange，提问作者kocebob436