Perform UPDATE from existing SELECT

2019-08-11 19:08发布

问题:

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.

The table structure is essentially:

id | judgeId | entrantId | roundId | rndFeedback 

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

output -

----------- ----------------------------------------------------------------------------------------------------------------------------------
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)

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

Thanks!

回答1:

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