-4

I have this query

UPDATE users SET
    objectName = 
        CASE WHEN VideoID =2 AND MusicID = 13 THEN 'Sing with Me!!!' END,
        CASE WHEN VideoID =2 AND MusicID = 2 THEN 'Awe!!!' END,

WHERE MusicID between 1 AND 13;

now 1 and 13 is repeated for every different videoID, i have 13 records for videoID 2, 13 with videoID 5 and so on and there is no Primary key here

trying to Update the values of the ObJectName Field based upon the values i pased, but the formation of the update is wrong.

not sure what could be the best way to do it, or if i do the above way, i get an error on the syntax error in the keyword CASE

  • Your question is off-topic since it is not related to database administration - SO is the appropriate forum. It would be best to put together a MCVE for others to understand your issue. – SMor Jun 24 at 14:36
  • its an update statement so let me try to replicate if i can dbfiddle.uk/… – niim Jun 24 at 14:51
  • created the fiddle but with 5 rows, not all 13, but concept is same – niim Jun 24 at 14:51
0

When you're setting the value of objectName, SQL Server expects 1 value, but you have multiple comma separate values being passed to it.

UPDATE users SET
    objectName = 
        CASE
            WHEN VideoID =2 AND MusicID = 13 THEN 'Sing with Me!!!'
            WHEN VideoID =2 AND MusicID = 2 THEN 'Awe!!!'
            ELSE ''
        END
WHERE MusicID between 1 AND 13;
| improve this answer | |
  • 1
    You need to cover the case of rows that have MusicID between 1 AND 13 but are not 2 or 13. For those rows, the objectName will still be updated, with NULL value. The OP may not want this. – ypercubeᵀᴹ Jun 24 at 20:07
  • I added an ELSE as a catch all. You're right though. – MrPshaw Jun 24 at 20:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.