Post reply

Using Update to change field values

  • July 28, 2006 at 8:14 am

    #94489

    Hi,

    I'm a newbie when it comes to SQL and new to this forum.  So, this is probably a simple syntax error or oversite on my part.  I am trying to write a query to change the value of a field for every record in a table.  Again, I'm new, so I got the code from a SQL book I've been reading. 

    Update CARD_Attached_Acct

           Set Institution_Number = 5

    I'm running this in Query Analyzer.  I'm attached to the correct database and when I "parse" the command it completes successfully.  However, when I run it, I get this error:

    Server: Msg 207, Level 16, State 1, Line 1

    Invalid column name 'Institution_Number'.

    I've checked and rechecked the table and the column is there and the spelling is correct.  I've tried a few variations (ie. prefixing the table name / dbo / both to the table name and the column name, but to no avail.  Any help would be greatly appreciated.

    Sincerley,

    Bryan Zimmerman

    bryan.zimmerman@fiserv.com

  • July 28, 2006 at 8:37 am

    #652129

    You probably have done this, but the table name is correct as well? 

    (Also, you are aware that  without a WHERE clause, you will be updating all records...) 

     

    I wasn't born stupid - I had to study.

  • July 28, 2006 at 9:12 am

    #652138

    Thanks for your reply.

    Yes, I've checked the table name and I want to change every record.  In my original post, I was not as clear as I should have been.  Actually, I'm trying to do this for 200+ tables and eventually would like to set it up for multiple databases.  When I try running the two lines by themselves, I get a completely different error:

    Server: Msg 547, Level 16, State 1, Line 1

    UPDATE statement conflicted with TABLE FOREIGN KEY constraint 'CARD_Acct_CIS_Attach_Accts_KEY'. The conflict occurred in database 'IA05Daily', table 'CIS_Attached_Accts'.

    The statement has been terminated.

    So, now I have two problems.  Again, any help is greatly appreciated.

    Sincerely,

    bz

  • July 28, 2006 at 9:25 am

    #652145

    Well kind of..., but really it is still one problem. 

    You will probably get better replies than I will give you, but here's a start. 

    You "may" need to ALTER the existing Triggers.  Drop the existing Triggers while you do the insert, then put them back [make sure your data integrity is not harmed by this action], review the Triggers and see if your FK needs some action taken...

    I think the entirety of you difficulties actually lies with the Triggers and how you will handle them...

    I wasn't born stupid - I had to study.

  • July 28, 2006 at 9:31 am

    #652147

    Thanks again.

    I'll try dropping the triggers but it will probably be Monday before I have a chance.  I wear more than one hat so it's a really balancing act trying to get things accomplished around here.

    Sincerely,

    bz

  • July 28, 2006 at 11:17 am

    #652172

    Try it in your Development db.  These Triggers are there for a reason and you may find later the data integrity or even you front-end is goofed up by dropping them...   Good luck 

    I wasn't born stupid - I had to study.

  • July 30, 2006 at 5:42 am

    #652249

    When you have a related table that has a key constraint that means you will more than likely create orphan records by doing the update you are trying.

    you should first update the related table records first then update the parent records.  you should do all of this in begin transaction and commit if you have no errors.

     

    something like this

    Begin Transaction

    update ChildTable set field = new value

    update ParentTable set Field = new Value

    Commit

     

  • July 31, 2006 at 5:43 am

    #652312

    not knowing all whats happening with your tables it hard to give a good answer but....

    first off...

    if your changing data that points to another table (foreign key FK) you have to make sure that data exists first.

    Dropping the FK's and recreating them after wont help there. I didnt catch how Triggers got in the conversation and i dont see how that would effect the FK Relationship problem

    so, with that said.....

    you normally would have to add a row to the table that contains Institution_Number in it to make sure one with the value of 5 exists

    then your update should work fine,

    good luck...

  • July 31, 2006 at 1:55 pm

    #652459

    I was able to get a dts package to change the field in one table, but only by specifying a value for the field.  I tried using a global variable, but it didn't work.  Of course, doing it this way isn't much of an option.  I'd have to create about 15-20 dts packages with 220+ data transforms, etc... It seems to be alot of work, just to change the value of one field.  When I get some more time, I'll give it another try.

    Thanks,

    bz

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply