SQL updating multiple rows in 1 table

Question

I have a question. I am using MS SQL Server Management Studio by the way.

I have a Dictionary table with a lot of translations. I need to copy a complete description from a languageID to another languageID.

Example below.

LanguageID | Description
    2      | Some text
    2      | More text
    2      | Some more text
   10      | *needs to be replaced
   10      | *needs to be replaced
   10      | *needs to be replaced

The result must be like this:

LanguageID | Description
    2      | Some text
    2      | More text
    2      | Some more text
   10      | Some text
   10      | More text
   10      | Some more text

The description of LanguageID 2 and 10 must be exactly the same.

My current Query runs into an error:

update tblDictionary
set Description = (Select Description from tblDictionary where 
tblDictionary.LanguageID = 2)
where LanguageID = 10

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression. The statement has been terminated.

your subquery retrieves more than one record because you only have the LanguageID = 2 in the where clause. — GuidoG
– GuidoG, Commented Feb 7, 2018 at 10:13
your select query returns rows more than 1 value. so, SQL confuses which row will be used. — Badiparmagi
– Badiparmagi, Commented Feb 7, 2018 at 10:14
I hope this is not the complete table layout. Can you post the entire table layout ? — GuidoG
– GuidoG, Commented Feb 7, 2018 at 10:15
You're missing relevant columns from your example. I'm willing to bet that the descriptions describe Something, and that Something is identified in the columns that you're not showing us. — MatBailie
– MatBailie, Commented Feb 7, 2018 at 10:23

GuidoG · Accepted Answer · 2018-02-07 10:39:46Z

4

If all translations for LanguageID 10 must be exact the same as for languageID 2 then its easier to delete all translations for ID 10 and then insert them back again.
Something like this

delete from tblDictionary where LanguageID = 10;

insert into tblDictionary (LanguageID, Description)
select 10, d.Description
from   tblDictionary d
where  d.LanguageID = 2

This method also has the advantage that if there are less records with LanguageID = 10 then there are for LanguageID = 2 this will be corrected in the same process.

If you have more columns in tblDictionary than you will need to modify the insert statement off course

edited Feb 7, 2018 at 10:39

answered Feb 7, 2018 at 10:19

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

@BasvanOoteghem - Don't trust anyone's answer to such questions. Set up your example in a testable environment and try it yourself. We're not here to do your work for you, and even if we do, trusting answers on the internet without rigorously validating them yourself is simply insane.

GuidoG Over a year ago

yes it will also work for null values. It will make an exact copy of languageID 2

Wince · Accepted Answer · 2018-02-07 10:32:25Z

1

DECLARE @temp varchar(50)
DECLARE language_cursor CURSOR FOR  
SELECT Description FROM tblDictionary  
WHERE LanguageID = 2  
ORDER BY Description;  

OPEN language_cursor;  

-- Perform the first fetch.  
FETCH NEXT FROM language_cursor
into @temp;  

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   update TOP (1) tblDictionary
   set Description = @temp
   where Description = ''
   and LanguageID = 10;  
   FETCH NEXT FROM language_cursor
   into @temp;
END  

CLOSE language_cursor;  
DEALLOCATE language_cursor;

Set all languageID 10 to empty first, then loop all description from languageID 2 to update into languageID 10 one by one until all empty description from languageID10 is filled.

edited Feb 7, 2018 at 10:32

answered Feb 7, 2018 at 10:27

Wince

213 bronze badges

4 Comments

Wince Over a year ago

yes, you are right...thanks for pointing out, i missed out the update top (1) from the query....GuidoG got a better solution up there...

Wince Over a year ago

i am using sql server 2012 and there is no syntax error on the code, it can be executed successfully...

GuidoG Over a year ago

May not perform good if the table has many records but it will work. Also there is no correction when there are less records with ID = 10 then are for ID = 2 but I upvoted anyway

Wince Over a year ago

yup, your solution is still the best in this case...i upvoted yours too....thanks for your upvote

MatBailie · Accepted Answer · 2018-02-07 10:54:31Z

0

Now if you really want an update, something like this should work, even though I think the structure of the table needs to be improved.

WITH l2 AS 
(SELECT *,  
 ROW_NUMBER() OVER(PARTITION BY LanguageId ORDER BY Description ASC) AS No FROM tblDictionary WHERE LanguageId=2),
l10 AS 
(SELECT *,  
 ROW_NUMBER() OVER(PARTITION BY LanguageId ORDER BY Description ASC) AS No FROM tblDictionary WHERE LanguageId=10)

UPDATE l10 SET Description = l2.Description 
FROM l10
INNER JOIN l2 ON l10.No = l2.No

edited Feb 7, 2018 at 10:54

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

answered Feb 7, 2018 at 10:37

user4219031

4072 silver badges4 bronze badges

Collectives™ on Stack Overflow

SQL updating multiple rows in 1 table

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related