SQL Question

Carepolice

Power Member
Joined
Mar 25, 2009
Messages
575
Reaction score
571
I know we have some very talented people when it comes to SQL in this community. Hopefully someone is able to lend a hand.

I need to move data from one column to another column with restrictions, and each row of data is likely to be different so it's important that they match the row they originated in.

To better explain:

Example of table (not actual data)

Name Number AmountOwed SourceTable NewColumn
Test 555555 12 052109_ABCD
Test 555555 16 052109_ABCD
Test 555555 13 052109_ABCD
Test 555555 17 052109_ABCD
Test 555555 13 052109_ABCD
Test 555555 11 052109_ABCD
Test 555555 12 052109_ABCD
Test 555555 16 052009_ABCD
Test 555555 13 052009_ABCD
Test 555555 17 052009_ABCD
Test 555555 13 052009_ABCD
Test 555555 11 052009_ABCD

I need to take the data in AmountOwed (for example) and copy it into NewColumn, then delete it from AmountOwed. The result should look like:



Name Number AmountOwed SourceTable NewColumn
Test 555555 052109_ABCD 12
Test 555555 052109_ABCD 16
Test 555555 052109_ABCD 13
Test 555555 052109_ABCD 17
Test 555555 052109_ABCD etc..
Test 555555 052109_ABCD
Test 555555 052109_ABCD
Test 555555 052009_ABCD
Test 555555 052009_ABCD
Test 555555 052009_ABCD
Test 555555 052009_ABCD
Test 555555 052009_ABCD


Keep in mind there are hundreds of thousands of rows inside this table and I need to modify only a select few (maybe 6 thousand)



Any assistance would be greatly appreciated.
 
well the first part is do:
update table xyz set NewColumn = AmountOwned;

Then
update table xyz set AmountOwned = null;

As far a subselecting a few thousand rows, you will have to put a where clause at the end of sql statements above, e.g. where xxx = yyy
 
remember to wrap your 2 updates inside a transaction or you may end up with total data loss
 
Good point, drax. Better yet, fully back up your data before doing it (if possible).
 
Thanks for your responses



remember to wrap your 2 updates inside a transaction or you may end up with total data loss


Could you please provide an example? I'm relatively new to SQL. Some additional information, the table in which these changes need to be made is constantly being utilized by over 50 applications and anything that could potentially tax this heavily will cause problems.
 
bro, are you sure you want to touch a table like this (being used by 50 apps) if you do not know enough about sql update statement? You are asking for trouble.
 
I'm 100% sure that I do not want to touch this table, however, I may have no choice as the DBA is unreachable and these changes need to take effect.
 
Ok, first you have be able to reach the tables.. do you have phpmyadmin, and can you see the tables.

After that - which I assume is already ok..

Please provide the condition that limits the rows, the name of the table, and the exact column names, we might be able to provide you exact statements to execute.

drax can provide you transaction blocks, I have not done that.

If the table is not currently being updated heavily, then do back it up first. Click on the table in phpmyadmin, go to 'operations' tab and copy it (data and structure) to something.

Then we can talk.
 
EDIT: Posted this before reading your response. This is mssql running on 2000, I have query analyzer.
EDIT2: Forget about the smileys, the date/time formats contain that smiley code


Alright, so this is what I'm planning on doing.


UPDATE contact (table name)
SET amountowed = weeklyrateamount
WHERE projectid in ('firstid','secondid','thirdid','fourthid')
AND importdate > '2009-05-16 16:02:43.000'
AND importdate < '2009-05-21 16:02:43.000'




Then this statement:


UPDATE contact (table name)
SET amountowed = null
WHERE projectid in ('firstid','secondid','thirdid','fourthid')
AND importdate > '2009-05-16 16:02:43.000'
AND importdate < '2009-05-21 16:02:43.000'

Does this look close?
 
Also, on the subject of backups. These tables are backed up nightly though a backup right now is out of the question and data loss would be a big problem. I will say that we run update statements all the time, I'm just not familiar with an update of this nature.
 
Yes, looks good. If your table name is xyz, then it would read:

UPDATE xyz
SET amountowed = ....

The second statement might fail if amountowed does not accept null, in that case replace it with '0' (without quotes), i.e.
SET amountowed = 0

Make sure to do a backup of the table per my prev. post.
 
I don't think you need transaction on this.. if you are ok with not backing up right now, up to you.
 
When you run it, and if it succeeds, make a note of # of rows that were updated.. this would help debugging later if needed.
 
UPDATE contact (table name)
SET amountowed = weeklyrateamount
WHERE projectid in ('firstid','secondid','thirdid','fourthid')
AND importdate > '2009-05-16 16:02:43.000'
AND importdate < '2009-05-21 16:02:43.000'


With this statement, should it read amountowed = weeklyrateamount, or the other way around? If weeklyrateamount is the column I'd like all the information from amountowed to copy over to, that is.

common sense leads me to believe it should be weeklyrateamount = amountowed as I'd like my target column to take on new values, but I may be wrong.
 
ok for mssql a transaction will do both in the same time, but it WILL lock the table while the update is running, which will block apps from accessing it. However, you will have complete data integrity. The reason you do this is if one of the applications update the table after you do the first update, it could screw things up a bit.

If you cant take a backup of the whole DB, you could just take a table backup.

SELECT * INTO contact_backup_20090521 FROM contact

to start and end a transaction to do
BEGIN TRANSACTION
and
END TRANSACTION


concerning your updates.. unless i misread your first post, they are wrong.. you are updating amountowed to the new new column, then wiping the amountowed out.. i assume you want to reverse one of them ;)

SELECT * INTO contact_backup_20090521 FROM contact;

BEGIN TRANSACTION;

UPDATE contact
SET weeklyrateamount = amountowed
WHERE projectid in ('firstid','secondid','thirdid','fourthid')
AND importdate > '2009-05-16 16:02:43.000'
AND importdate < '2009-05-21 16:02:43.000';

-- overhere i would do a SELECT * FROM contact and ensure the data is good
-- if its not good you can ROLLBACK TRANSACTION to revert to before

UPDATE contact
SET amountowed = 0
WHERE projectid in ('firstid','secondid','thirdid','fourthid')
AND importdate > '2009-05-16 16:02:43.000'
AND importdate < '2009-05-21 16:02:43.000'

-- overhere i would do a SELECT * FROM contact and ensure the data is good
-- if its not good you can ROLLBACK TRANSACTION to revert to before
END TRANSACTION;


Having said this, I agree it's not the best case for a non DBA to do direct updates, but if you have no choice, you have no choice ;)
 
Thanks for all of the information guys, I have been given the ok to postpone these changes until after hours when an impact on applications won't be devestating. I'll keep this thread updated, and thanks again I really appreciate the assitance.
 
Back
Top