|
Hi, I have two int columns in a table that have a sample data as below
and I need to update these columns with
1).If ColA has a non negative number then update ColB with 0 (zero).
2).If ColA has a negative number then update ColB with the same value
that ColA has.
Can some please help me with sql?
Thanks in advance.
L
-------------------------
ColA | ColB
-----------|------------
-1 -1
-1 -1
-3 -1
4 -1
-1 -1
7 -1
3 -1
73 -1
-3 -1
39 -1
32 -1
35 -1
3 -1
31 -1
3 -1
397 -1
400 -1
403 -1
406 -1
-1 -1
412 -1
436 -1
421 -1
421 -1
424 -1
-1 -1
430 -1
433 -1
-1 -1
415 -1
442 -1
445 -1
-1 -1
-3 -1
487 -1
523 -1
568 -1
472 -1
475 -1
478 -1
-3 -1
490 -1
493 -1
496 -1
502 -1
505 -1
511 -1
514 -1
517 -1
520 -1
-1 -1
526 -1
538 -1
541 -1
544 -1
556 -1
559 -1
550 -1
457 -1
574 -1
-1 -1
580 -1
583 -1
586 -1
589 -1
595 -1
625 -1
604 -1
625 -1
610 -1
-1 -1
622 -1
625 -1
-1 -1
-1 -1
637 -1
-1 -1
649 -1
652 -1
655 -1
658 -1
661 -1
664 -1
667 -1
670 -1
673 -1
-1 -1
685 -1
688 -1
691 -1
697 -1
700 -1
703 -1
709 -1
712 -1
715 -1
718 -1
721 -1
-1 -1
-3 -1
-1 -1
-1 -1
-1 -1
-1 -1
|
|
|
|
|
I would use two Update statements.
Begin Transaction
update MyTable
set ColB = 0
where ColA > 0
update MyTable
set ColB = ColA
where ColA < 0
By using a transaction you can review the number of rows affected and decide whether you want to
Commit or Rollback the transaction.
><david><
|
|
|
|
|
What if ColA == 0?
I may or may not be responsible for my own actions
|
|
|
|
|
One version is:
UPDATE TableName
SET ColB = CASE
WHEN ColA < 0 THEN 0
ELSE ColA
END;
Correction based on the good feedback by musefan:
UPDATE TableName
SET ColB = CASE
WHEN ColA >= 0 THEN 0
ELSE ColA
END
The need to optimize rises from a bad design.My articles[^]
modified on Wednesday, March 23, 2011 12:16 PM
|
|
|
|
|
I think you misread the requirement (even thou you got an accepted answer), ColB should be 0 for NON-negative numbers
I may or may not be responsible for my own actions
|
|
|
|
|
You're right for non-negative it's vice versa. I think the OP still got the idea. 
|
|
|
|
|
UPDATE table SET colB = DECODE( SIGN( colA ), -1, colA, 0 )
|
|
|
|
|
Reading excel file with OPENROWSET of sql server.
Working fine and getting values.
Now, if the file which i am reading is in use (opened), it gives error.
How should i capture this error, I tried
Declare @error varchar(50)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\webex\cis\CIS overview.xls',
'SELECT * FROM [CIS Overview$]')
IF(@@error <> 0 )
BEGIN
Set @error = 'Error reading file'
RETURN
END
But its not even reaching to IF clause,
terminate giving message
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
How can i capture this error ?
reagards
|
|
|
|
|
|
Any other Light ?
As i am using sql 2000 
|
|
|
|
|
Okay, and you're sure the IF is not hit at all... One thing you could try is to call this procedure from another and check if you can handle the error properly in the calling procedure.
|
|
|
|
|
Mika Wendelius wrote: you're sure the IF is not hit at all...
Well instead of if you can use PRINT, if control reaches to print it should print.
But execution terminates on OPENROWSET itself.
Mika Wendelius wrote: call this procedure from another and check if you can handle the error properly in the calling procedure.
No luck !
in a new query window i tried like
EXEC [spName]
IF(@@error <> 0)
BEGIN
Print 'No error'
END
ELSE
BEGIN
Print 'error'
END
here also not going to if clause !
Any other suggestion?
|
|
|
|
|
Hmm, what happens if you try to execute the statement dynamically. Put it into a string and then use exec to run the statement. Unfortunately I don't have SQL Server 2000 to test this issue...
|
|
|
|
|
Hi,
I am trying to implement a dialog for updating some data in a DB. The OK button should actually save the data to the DB, while the Cancel button should discard the changes. The problem is, I need to be able to read the changed but not yet committed data within the dialog. I have read much information about transactions, isolation levels etc. and if I understand it correctly, the only way to achieve this is to wrap all the changes in a single transaction and use the READUNCOMMITED isolation level for it. Then in the OK button handler I'd just commit the transaction and in the Cancel button handler I'd call rollback.
However, since I am using the SQLite.NET library, I am only able to use READCOMMITTED or SERIALIZABLE levels. What's more, in some other piece of software I have which uses MS Access database and solves exactly the same problem the transaction is started with the READCOMMITED isolation level and everything works as intended. That really puzzles me. 
Do you have any suggestions/advice for this kind of problem?
|
|
|
|
|
Perhaps I misunderstand your question, but the whole idea of using a dialog is to let the user determine the variables.
Once this is done, it's easy enough to validate the input in your code, before even creating a query.
My advice is free, and you may get what you paid for.
|
|
|
|
|
The dialog doesn't just show some variables. It lists (complex) objects from the database and should allow adding/deleting/editing them. When he adds a new object he might decide to edit it before leaving the dialog, so I need to be able to store its properties somewhere. Of course I could hold the references to the new objects in my code, but I thought the most elegant way would be to store them in the DB immediately and access them in a unified way. Of course I still need the option to roll back all the changes when the user hits Cancel.
|
|
|
|
|
Well, the basic choice is to keep all the data in client memory, or server memory, or write the whole thing to disk, right?
If you have lots of clients sending transactions to a database server at the same time, I'd try and do all the work on the client.
If the user might use a lot of time performing a transaction, a simple and robust solution could be to store the whole transaction in a temporary table (or structure of tables). That way you can perform some of the validation directly on input in your app, and then when the user clicks ok, you can still run some validation first between transactions from different users, and then commit the transaction to the live tables after that. If the user clicks cancel just delete the object from the temporary table.
An added advantage of this method, is that you can think about offering the user "template" or "favorite" transactions.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Sure, it's always an option (albeit a cumbersome one) to store all the changes in the client code until the user hits OK or Cancel. I just wanted to know whether it is possible to set a "restore point" and later roll back all the changes up to that point. The database is single-user, so there won't be any read/write conflicts.
|
|
|
|
|
The first rule of transactions is: Never have a conversation with the user while a transaction is active!
So what you should do is to ask all the necessary questions before you start the db operations, perhaps show what's in the database now and how it's going to be changed etc. After the last yes/no question, then do all the operations at once and if everything goes fine, commit otherwise rollback.
In my opinion (I know many disagree) Read uncommitted should be banned for good. In a well designed system there's no need for RU isolation level and as you mentioned many of the database products don't even support it (inlcude Oracle in your list).
Having UI conversations etc while you have placed locks in the database typically causes prolonged transactions, slows the system down and decreases parallelism because of locking issues.
|
|
|
|
|
I am not sure if I am phrasing the question right, which is probably also why I haven't been able to find an answer anywhere.
The situation is as follows: I have an ERP system using an MS SQL 2005 database.
There are several tables in the database that contain document data (i.e. orders, deliveries, invoices, etc). These tables get a lot of read and write action under normal use of the ERP system.
There are several heavy ad hoc queries saved in the system, and also some user applications and timed console apps that run heavy queries (sales reports, purchase forecasts, and such).
The result is that in daily use such a strain is placed on the document tables that we get constant timeouts, and/or applications (and the ERP system itself) grinding to a halt when too many users / applications are using the system at the same time.
The question is: Considering that I can hardly or not at all touch database settings, and I certainly can not fool around with the table structures themselves, I am thinking of making our applications and ad hoc queries not perform read operations directly on the tables in question. Also most of the data should be up to date to within say an hour.
What are my options ? Would using views or a synchronized db help ? Any other solution ?
Cheers,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
One option is to publish a copy of the tables to a second database for reporting. See Replication in BOL.
Also tuning of the queries might help. If the reports are a job move the time slightly.
Good luck,
djj
|
|
|
|
|
Thanks for the tip ! I looked into publishing a little, and it definitely looks promising.
Cheers,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Sorry I could not give more info but I have only played around with publish replication (no actual usage). We do log shipping which also could work but I think the publish would be better for you.
|
|
|
|
|
Don't worry about it, I don't mind doing the research. That nudge in the right direction was really all I needed.
My advice is free, and you may get what you paid for.
|
|
|
|
|
There are a huge number of things you can do to tune the performance on a database. It sounds like you have a major corporate database and no real skills (DBA level) to service the thing. I STRONGLY suggest hiring a consultant (finding a good one can be a challenge) to look at the infrastructure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
