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.
| Author |
Topic |
|
rogerbacon
Starting Member
2 Posts |
 Posted - 2012-05-31 : 12:24:45
|
| Hi. I'm trying to export my data out of an old OLD database program that uses an unknown data format. It has an option to export into Excel, which is fine, but it will only export entries where 'quantityOqned' > 0. Since I want to use this data as the basis for a program I am writing, I need all entries, even those with value zero. So, I thougth I would temporarily change the 'quantityOwned' value to 999 where it equals 0 with this SQL statement:Update Cards set QuantityOwned = '999' where QuantityOwned = '0' ;When I execute that statement The program locks up. Any idea what is wrong? Can I update a field based on that field (QuantityOwned using QuantityOwned)? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 13:11:15
|
| How big is it?Could be that it is just taking a long time.Are there any triggers which could slow it down a lot.Try just updating a few rows then increase the batch size and see what happens. You might have to loop updating a batch of rows at a time.Is this based on sql server or something else? It is possible that statewment is invalid or just very inefficient in whatever yoou are using.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rogerbacon
Starting Member
2 Posts |
Posted - 2012-05-31 : 14:44:31
|
quote:
Originally posted by nigelrivett
How big is it?Could be that it is just taking a long time.Are there any triggers which could slow it down a lot.Try just updating a few rows then increase the batch size and see what happens. You might have to loop updating a batch of rows at a time.Is this based on sql server or something else? It is possible that statewment is invalid or just very inefficient in whatever yoou are using.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
It's about 9 mb and 12,000 entries. I've run similar updates on the database before using statements like Update Cards set QuantityOwned = '4' where Edition = 'SomeEdition';and they worked fine and only took a few seconds. I let it run for 12 minutes and it didn't finish so I think there was something wrong. Is it because I'm updating a field and using that field as the criteria for the update? Does that create some sort of dependancy loop?I have no idea what kind of database the program is using as it is imbedded in it. I'm just trying to update the table so I can export it to Excel. |
|
|
|
|
|
|
How to update quantity based on quantity