从现有的 SELECT 执行更新 [英] Perform UPDATE from existing SELECT

问题描述

我有一个表格，其中包含有关多个波段的数据.每个乐队在这张表中都有很多记录，其中包含各种信息以及一些评委对他们的重要反馈.

I have a table which contains data about a number of bands. Each band has numerous records in this table, which contain various bits of info and importantly feedback for them from some judges.

表结构本质上是:

id | judgeId | entrantId | roundId | rndFeedback 

<小时>

以下 SQL 获取每个波段的所有反馈并将其整理成单个记录.例如:

---

The following SQL grabs all the feedback for each band and collates it into a single record. eg:

DECLARE @t TABLE (
    id INT,
    judgeId INT,
    entrantId INT,
    roundId INT,
    rndFeedback VARCHAR(100)
)

INSERT INTO @t
VALUES 
    (1, 5 , 22, 2, 'Awesome'),
    (1, 4 , 22, 2, 'Really Nice Work'),
    (1, 9 , 22, 2, 'The bass was a little heavy'),
    (1, 10, 22, 2, 'You Suck'),
    (1, 11, 22, 2, 'It was really good but lacking emotion'),
    (1, 14, 22, 2, 'You get my vote'),
    (1, 15, 22, 2, 'Nice Melody'),
    (1, 4, 23, 2, 'TEST'),
    (1, 15, 23, 2, NULL),
    (1, 4, 24, 2, NULL)

SELECT t1.entrantId, STUFF((
    SELECT ' ' + rndFeedback
    FROM @t t2
    WHERE t2.entrantId = t1.entrantId
        AND t2.roundId = 2
        AND t2.rndFeedback IS NOT NULL
    FOR XML PATH('')), 1, 1, '')
FROM (
    SELECT DISTINCT entrantId
    FROM @t
    WHERE roundId = 2
        AND rndFeedback IS NOT NULL
) t1

输出 -

----------- ----------------------------------------------------------------------------------------------------------------------------------
22          Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody
23          TEST

但是，我现在希望能够接受这些反馈并更新该乐队的所有唱片，以便每张唱片都包含针对该艺术家的相同的整理反馈.

But, I now want to be able to take this feedback and update all the records for that band so that every record would contain the same collated feedback for that artist.

例如，表格现在看起来像:

So for example the table would now look like:

    (1, 5 , 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 4 , 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 9 , 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 10, 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 11, 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 14, 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 15, 22, 2, 'Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody'),
    (1, 4, 23, 2, 'TEST'),
    (1, 15, 23, 2, 'TEST'),
    (1, 4, 24, 2, NULL)

我不知道如何去做，因为这超出了我目前的 t-sql 知识水平.

I've no idea how to go about doing this, as this is getting beyond my current level of t-sql knowledge.

谢谢！

推荐答案

DECLARE @t TABLE (
    id INT,
    judgeId INT,
    entrantId INT,
    roundId INT,
    rndFeedback VARCHAR(MAX) -- <<<
)

UPDATE t1
SET rndFeedback = NULLIF(STUFF((
    SELECT CHAR(13) + rndFeedback
    FROM @t t2
    WHERE t2.entrantId = t1.entrantId
        AND t2.roundId = 2
        AND t2.rndFeedback IS NOT NULL
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '')
FROM @t t1
WHERE roundId = 2

这篇关于从现有的 SELECT 执行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！