CODEWITHSUNDEEP
c#asp.netsql
Chris Cudmore
Chris Cudmore

Reputation: 30181

Update multiple rows into SQL table

Suppose there is a fully populated array of data String[n][3] myData.

I want to do this:

for (String[] row : myData)
{
   SQL = "update mytable set col3 = row[2]
   where col1 = row[0] and col2=row[1];" 
}

Obviously I've left a lot out, but I want to express the idea as succinctly as possible.

Is there a simple way of doing this in a single DB command? How about a not so simple way?

EDITS: Data is not coming from another table (it's a web form submission - Multiple Choice exam)
Seeing as the app is web facing, It's got to be injection proof. Parameterized Queries are my preferred way of going.
I'm using MS-SQL Server 2005

EDIT:Closing, and re-asking as Multiple DB Updates:

EDIT: Re-opened, as this appears to be a popular question

Upvotes: 2

Views: 34044

Answers (10)

fmsf
fmsf

Reputation: 37177

You can make a big string like:

for (String[] row : myData)
{
   SQL += "update mytable set col3 = row[2]
   where col1 = row[0] and col2=row[1];" 
}

sqlDriver.doInsertQuery(SQL); // change this to your way of inserting into the db

And just commit it all at once. I'm not very good with SQL so that's how I would do it.

The SQL engine will just split it by the ';' and do separate inserts on its own. It's OK to add it all in a string though. It's kind of the same as if you copy a big string with multiple updates/inserts into the SQL prompt.

Upvotes: 1

helios
helios

Reputation: 13841

That looks like you want to make an update A, over rows that has coditions B and C. (A, B, C) are stored as tuples (rows) in myData. Isn't it?

Maybe (if you're using Microsoft SQL Server... I don't know if it exists in Oracle, could be) you can use a JOIN with an UPDATE. You can declare an update over a table joined with another one. If myData comes from another table then you could do (it's not the correct syntax) :

UPDATE whatchanges wc INNER JOIN changes c ON <yourcondition>
SET wc.col1 = c.newvalue
WHERE ....

(if you want to apply all changes in "changes" table you don't have to use WHERE of course, the INNER JOIN already has selected the correct rows).

Of course there are limitations to this kind of update. And it's MS SQL proprietary. So if it's your case I'd suggest to look for it on MS web (keywords: UPDATE and JOIN)

Upvotes: 1

Hafthor
Hafthor

Reputation: 16916

emit an update that goes against a values table:

UPDATE myTable SET col3=c FROM myTable JOIN (
  SELECT 1 as a, 2 as b, 'value1' as c UNION ALL
  SELECT 3 as a, 4 as b, 'value2' as c -- etc...
) x ON myTable.col1=x.a AND myTable.col2=x.b

so you just put this together like this:

// make one of these for each row
String.Format("SELECT {0} as a, {1} as b, '{2}' as c", 
  row[0], row[1], row[2].Replace("'","''")) 

// put it together
string expr = "UPDATE myTable SET col3=c FROM myTable JOIN (" +
  String.Join(stringformatarray, " UNION ALL ") +
  ") x ON myTable.col1=x.a AND myTable.col2=x.b"

or you can use StringBuilder to put this together.

and then, of course, you execute this one string.

Upvotes: 0

cmsjr
cmsjr

Reputation: 59245

If for whatever reason you can't perform the update using one of the methods suggested above, the highly inefficient approach below would probably work for you. 

SQL = "Update myTable Set Col3 = Case " 
for (String[] row : myData)
{
   SQL += "When Col1 = " + Row[0] + " and Col2 = " + Row[1] + " then " + row[2] + " "   
}
SQL + = "Else Col3 end"

Upvotes: 1

Rob Allen
Rob Allen

Reputation: 17749

If you are using Enterprise Library in your data access layer, you can create the transaction in .Net, iterate through your procedure calls, then commit/rollback all from .Net.

DbTransaction transaction = connection.BeginTransaction();
try
{
    for (String[] row : myData)
    {
        ListDictionary params = new Specialized.ListDictionary();
        params.add("@col3", row[2]);
        params.add("@col1", row[0]);
        params.add("@col2", row[1]);
        executeNonQuery("myUpdateProcedure", params);
    }

    transaction.commit();

}
catch(Exception ex)
{
    transaction.rollback();
    throw ex;
}
finally
{

    connection.close();
}

Upvotes: 1

Kevin Berridge
Kevin Berridge

Reputation: 6303

It depends on what database you are using. If you're using SQL Server 2008, you can use stored procedure TABLE parameters. This allows you to pass all your values into the stored procedure in a single table, then you can do:

update mytable set mytable.col1 = @tbl.col1
  from mytable 
  inner join @tbl on mytable.col2 = @tbl.col2

If you're using SQL Server 2005, you can use XML. Format your values as XML, then use XQuery statements (ie, 'nodes' and 'value') to parse out the XML. This can also be done in a single SQL statement, and it doesn't require a stored procedure.

Upvotes: 7

Ted Elliott
Ted Elliott

Reputation: 3503

If you are using Sql Server you can use SqlBulkCopy. You would first have to put your data in a DataTable, which would be pretty easy since you already have it in a string array.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Upvotes: 3

theraccoonbear
theraccoonbear

Reputation: 4337

I suspect you will need to use multiple SQL statements. You may find a wrapper to handle the minutiae but underneath I'd imagine it'd iteratively run a SQL statement for each UPDATE.

Upvotes: 0

extraneon
extraneon

Reputation: 23980

This may not be the answer you want, but opening a transaction, executing your statements and then committing the transaction would, from a database point of view, do what you describe.

The state of the database does not change for other users of the database until the transaction has been completed, and that probably is the preferred effect.

Upvotes: 1

EndangeredMassa
EndangeredMassa

Reputation: 17528

Not really. You could create the string with the same loop, then pass your values as parameters, but that will still be multiple database commands.

for each whatever
    sql += "UPDATE ... ;"
end for
execute (sql)

Upvotes: 0

Related Questions