Update multiple rows in sql database at once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Update multiple rows in sql database at once

Author

Topic

harmony
Starting Member

4 Posts

Posted - 2010-05-11 : 23:12:21

hi! i have a database named tblAbra which has columns id,project,january,february..
now i populate the 1st 3 columns by importing the data from excel..the 1st 3 columns are populated with the data while the february rows are set to null..now i want to update the february column which are set to null by importing data from excel..i've tried this code:
cmd.CommandText = "UPDATE [tblAbra] SET february=(SELECT A.[february] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;Database=D:\sample_project\SPRS\Book1.xls;HRD=YES','SELECT * FROM [Sheet1$]') AS A) WHERE ID BETWEEN 388 AND 729"

but it returns me this error:
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.

pls help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 01:30:50

The update should be done using join.
Give table structure, sample data and wanted result please.

No, you're never too old to Yak'n'Roll if you're too young to die.

harmony
Starting Member

4 Posts

Posted - 2010-05-12 : 02:55:40

this is my data from excel:
project january february
a 1
b 2
c 3

the 1st thing im going to do is import this excel file and insert it to my database named tblAbra..my february column is currently set to null since there are no existing values yet..now after say a month i have my february column from excel is populated by the user so my excel data becomes likes this..
project january february
a 1 10
b 2 20
c 3 30
since i hve already data for my column february i am now going to import this from excel and update my february column of my tblAbra database which is previously set to null..so that my tblAbra database now should look like this:
project january february
a 1 10
b 2 20
c 3 30

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 03:06:33

Try something like this:


UPDATE ta
SET [february]=A.[february]
from [tblAbra] as ta

join
(SELECT ProjectID, [february] 
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                 'Excel 5.0;Database=D:\sample_project\SPRS\Book1.xls;HRD=YES',
                 'SELECT * FROM [Sheet1$]') 
) AS A 
on A.ProjectID = ta.ProjectID

-- Don't know if you need this:
WHERE ta.ProjectID BETWEEN 388 AND 729

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:39:10

I think you need to extend webfreds suggestion and look for each months value and update all whose value is different in excel.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harmony
Starting Member

4 Posts

Posted - 2010-05-12 : 23:36:26

it doesnt work..since the values to be inserted in the column february are different from each other..it's like inserting new values to column february at once..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:38:47

so do you mean you need to do just a batch update from excel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harmony
Starting Member

4 Posts

Posted - 2010-05-13 : 23:03:59

yes that's what i wanted to do..a batch update from excel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources