Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UPDATE table from another table (SQL Server 2000)

    I made a mistake! <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    I was asked to add a bunch of stuff to a webform (which writes to a SQL Server table) and forgot to include the new fields in my programming to transfer the info from one table to another. This was fixed today but now I have a lot of records in the table with the copied info with blanks where the new data fields had been assiduously gathering data. Now what I need to do is cycle thru the two tables and, if the row ID matches, UPDATE the copy table with the specific row information. I'm having a little trouble grokking how to do this in SQL Server. Access/ADO/VBA would work but is there a solution for setting up some kind of look with an update command all in SQL Server? I'm sure there is but not sure where it is, etc.

    Another little problem here is the db is, indeed, live so I can't (well, I could but I don't want to) disconnect the db whilst doing an update to the records. I was going to copy the beast into my local SQL Server installation for testing/poking but the copy db wizard keeps failing -- perhaps because the db is attached and humming along.

    Also, is it advisable to simple set up such an operation in Access on the tables as linked objects?

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    You should be able to do it with a T-SQL statement in Query Analyzer, or you could use the DTS to do it, though that get's a little arcane. You could also do it using Attached tables in Access, which I usually do because I find the Query Tool in Access much more intuitive than the SQL Server tools. You could start that way, then look at the SQL generated, and then copy that to SQL Query Analyzer, but you will find some syntax differences in the way Updates are done compared with Access, so that might be a "learning exercise."

    As to creating a test database, you should be able to use DTS to do that with no real problems, though you do need to create the test database first, and then use DTS to copy all of the objects. Hope this helps.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    I don't know if a simple SQL Update is going to work, as i need to go thru each row in Table 1 and update the values in a set of columns in Table 1 to the same, but blank, columns in Table2. This is the sort of thing that can be done in a module using Access/Linked Tables or even using ASP. Don't really need to do it in SQL Server, really. I was wondering if there's some nifty trick native to SQL Server that could handle this sort of thing.

    Which I would be doing, but, maddenlingly enough, SQL Server suddenly denied my long-used login profile access to objects in the db. It is now demanding my code fully qualify db objects with the owner prefix along with the name. I have TONS of code in ASP and none of it is fully-qualified, so this is a big problem. Do you know what I need to do to get the login profile and/or SQL Server to relax a little on this requirement?

    Also, when I try to create a copy of a db to my local SQL Server, the system errors with something like 'CANNOT CREATE VOLUME OMEWWIZ'. It also keeps saying there's already an .MDF file with the same name in the target path, which (AFAICS) wrong. I've never done this before, but my believe is that this would create a complete duplicate of the source db, including queries, stored procs, etc. Doing an import converts my queries to tables, which is not very useful...

    I dunno, this is one of those days where the fires are waterproof. <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UPDATE table from another table (SQL Server 2000)

    It sounds like you want to do an update query on Table2 from Table1 on some kind of join or match where the specified columns in Table2 are null, right? That is a fairly straightforward update query unless you have skated over some details in your explanation. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    yeah, that's what I'm trying to do. IN Access, the query grid wants you to put some kind of actual info in the fields to update, as far as I can tell. I need to get this done sometime soon (I had a big system meltdown that I'll blame on SQL Server) and have gotten that fixed. Now I need to run this update dealie.

    I'll look further into it, but I'm a bit confused on what the SQL would look like.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UPDATE table from another table (SQL Server 2000)

    T-SQL or Access SQL? Are you trying to do this from the front end or using a stored procedure? And I don't understand the first part of your post. You can't update a field without putting something into or taking something out, so what do you mean by
    <hr>the query grid wants you to put some kind of actual info in the fields to update<hr>
    It sounds like you've left out a few critical details.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    Oh, heck -- I think I have it figured out. You need to put in the table name.column name in the criteria in the Query Builder grid, which I thought I had tried before but apparently not. Now Access is building something that looks correct...

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    Steve,

    Is this sort of what you're trying to do?

    UPDATE table1
    SET table1.DateField = Table2.DateField
    FROM Table1
    INNER JOIN Table2 ON
    Table2.CustID = Table1.CustID AND
    Table2.PurgeDate = Table1.PurgeDateAND
    Table2.Ord_Number = Table1.Ord_Number AND
    Table2.Line_No = Table1.Line_No


    where: table1 = table you're updating and
    table2 = table you're getting the data from

    I took this from a T_SQL procedure I'm running but changed the fields to protect the innocent.
    Carpy Diem, it&#39;s .

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    yeah that looks about right. In this case, I just want to push all the data in 10 columns from one table to another. I have this:

    UPDATE sskelton_Reported_Limited_Gen_Inv INNER JOIN dbo_Edit_Recipient ON sskelton_Reported_Limited_Gen_Inv.SubjectDetailsID = dbo_Edit_Recipient.SubjectDetailsID SET sskelton_Reported_Limited_Gen_Inv.Recipient_Father s_Name = [dbo_Edit_Recipient].[Recipient_Fathers_Name], sskelton_Reported_Limited_Gen_Inv.Recipient_Father s_SSN = [dbo_Edit_Recipient].[Recipient_Fathers_SSN], sskelton_Reported_Limited_Gen_Inv.Recipient_Father s_DOB = [dbo_Edit_Recipient].[Recipient_Fathers_DOB], sskelton_Reported_Limited_Gen_Inv.Recipient_Mother s_Name = [dbo_Edit_Recipient].[Recipient_Mothers_Name], sskelton_Reported_Limited_Gen_Inv.Recipient_Mother s_SSN = [dbo_Edit_Recipient].[Recipient_Mothers_SSN], sskelton_Reported_Limited_Gen_Inv.Recipient_Mother s_DOB = [dbo_Edit_Recipient].[Recipient_Mothers_DOB], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Name1 = [dbo_Edit_Recipient].[Recipient_Child_Name1], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ DOB1 = [dbo_Edit_Recipient].[Recipient_Child_DOB1], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ SSN1 = [dbo_Edit_Recipient].[Recipient_Child_SSN1], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Gender1 = [dbo_Edit_Recipient].[Recipient_Child_Gender1], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Name2 = [dbo_Edit_Recipient].[Recipient_Child_Name2], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ DOB2 = [dbo_Edit_Recipient].[Recipient_Child_DOB2], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ SSN2 = [dbo_Edit_Recipient].[Recipient_Child_SSN2], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Gender2 = [dbo_Edit_Recipient].[Recipient_Child_Gender2], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Name3 = [dbo_Edit_Recipient].[Recipient_Child_Name3], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ DOB3 = [dbo_Edit_Recipient].[Recipient_Child_DOB3], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ SSN3 = [dbo_Edit_Recipient].[Recipient_Child_SSN3], sskelton_Reported_Limited_Gen_Inv.Recipient_Child_ Gender3 = [dbo_Edit_Recipient].[Recipient_Child_Gender3];

    in Access but it looks like I accidentally reversed the fields. As you can see, there's a LOT of potential typing involved...

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UPDATE table from another table (SQL Server 2000)

    If the tables have the same structure, meaning the same field names, then the table name is required any time you reference the field. It's always needed in the criteria to distinguish the field from a parameter.
    Charlotte

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    well, that only makes sense! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I think I'll hammer the query out using imported tables and if it looks good, hook into SQL Server and run on the actual tables.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: UPDATE table from another table (SQL Server 2000)

    To cut it down further you could use an alias for each table name, like :

    UPDATE sskelton_Reported_Limited_Gen_Inv as O
    INNER JOIN dbo_Edit_Recipient as I ON O.SubjectDetailsID = I.SubjectDetailsID
    SET
    O.Recipient_Fathers_Name = I.[Recipient_Fathers_Name],
    O.Recipient_Fathers_SSN = I.[Recipient_Fathers_SSN],
    O.Recipient_Fathers_DOB = I.[Recipient_Fathers_DOB],
    O.Recipient_Mothers_Name = I.[Recipient_Mothers_Name],
    O.Recipient_Mothers_SSN = I.[Recipient_Mothers_SSN],
    O.Recipient_Mothers_DOB = I.[Recipient_Mothers_DOB],
    O.Recipient_Child_Name1 = I.[Recipient_Child_Name1],
    O.Recipient_Child_DOB1 = I.[Recipient_Child_DOB1],
    O.Recipient_Child_SSN1 = I.[Recipient_Child_SSN1],
    O.Recipient_Child_Gender1 = I.[Recipient_Child_Gender1],
    O.Recipient_Child_Name2 = I.[Recipient_Child_Name2],
    O.Recipient_Child_DOB2 = I.[Recipient_Child_DOB2],
    O.Recipient_Child_SSN2 = I.[Recipient_Child_SSN2],
    O.Recipient_Child_Gender2 = I.[Recipient_Child_Gender2],
    O.Recipient_Child_Name3 = I.[Recipient_Child_Name3],
    O.Recipient_Child_DOB3 = I.[Recipient_Child_DOB3],
    O.Recipient_Child_SSN3 = I.[Recipient_Child_SSN3],
    O.Recipient_Child_Gender3 = I.[Recipient_Child_Gender3];

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •