1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Question

Discussion in 'BlackHat Lounge' started by Carepolice, May 21, 2009.

  1. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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.
     
  2. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    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
     
  3. drax

    drax Junior Member

    Joined:
    Aug 2, 2008
    Messages:
    124
    Likes Received:
    19
    remember to wrap your 2 updates inside a transaction or you may end up with total data loss
     
  4. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    Good point, drax. Better yet, fully back up your data before doing it (if possible).
     
  5. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    Thanks for your responses




    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.
     
  6. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    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.
     
  7. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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.
     
  8. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    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.
     
  9. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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?
     
  10. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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.
     
  11. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    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.
     
  12. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    I don't think you need transaction on this.. if you are ok with not backing up right now, up to you.
     
  13. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    When you run it, and if it succeeds, make a note of # of rows that were updated.. this would help debugging later if needed.
     
  14. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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.
     
  15. blackieman

    blackieman Power Member

    Joined:
    Jan 28, 2008
    Messages:
    762
    Likes Received:
    79
    set toColumn = fromColumn
     
  16. purebackend

    purebackend Newbie

    Joined:
    May 10, 2009
    Messages:
    37
    Likes Received:
    46
    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 ;)
     
  17. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    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.