[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to update a table with information from another table?

Posted on 2007-10-09
8
Medium Priority
?
256 Views
Last Modified: 2010-03-20
SQL Server 2005 or MS Access 2003 (SQL Queries)

How would I update a table with information from another table, specifically a one to many relationship. Example:
TABLE [ACCT]
ACCTNBR  NAME    CREDITLIMIT-QTR1      CREDITLIMIT-QTR2
1                Joe         0                                  0
2                Fred       0                                  0
3                Diane      0                                  0

TABLE [CREDITLIMIT]
ACCTNBR    EFFDATE   LIMIT
1                  1/30/07       20000
3                  1/30/07       25000
2                   2/15/07      30000
3                   2/10/07      40000
3                   3/31/07      20000
3                   6/30/07      15000
2                   5/30/07      10000

I would like to update TABLE [ACCT] with the correct amounts from TABLE [CREDITLIMIT].  I'm not sure, but I think I would use something like a select Max statement?  Any ideas?
0
Comment
Question by:irishm20
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20044632
This should get you quarter and credit limit:

    SELECT Format(creditlimit.effdate,"MM")/4 AS Quarter, acctnbr, Max(creditlimit.limit)
    FROM creditlimit
    Group By Format(creditlimit.effdate,"MM")/4, acctnbr

This will insert quarter 1:

    UPDATE acct
    SET creditlimit-qtr1 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 1
        AND creditlimit.acctnbr = acct.acctnbr)

Quarter 2...:

    UPDATE acct
    SET creditlimit-qtr1 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 2
        AND creditlimit.acctnbr = acct.acctnbr)
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20044639
Sorry, the last one should have been:

Quarter 2...:

    UPDATE acct
    SET creditlimit-qtr2 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 2
        AND creditlimit.acctnbr = acct.acctnbr)
0
 

Author Comment

by:irishm20
ID: 20044767
I'm getting a "Operation must use updateable query" error.  Any ideas?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20044890
what datatypes are these?
creditlimit
qtr2
0
 

Author Comment

by:irishm20
ID: 20045106
In this case, [creditlimit-qtr2] is one field and it's a float.
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 20047935
Does this query successfully get quarter and credit limit?

    SELECT Format(creditlimit.effdate,"MM")/4 AS Quarter, acctnbr, Max(creditlimit.limit)
    FROM creditlimit
    Group By Format(creditlimit.effdate,"MM")/4, acctnbr
0
 

Author Comment

by:irishm20
ID: 20049509
Thanks Anelp1ay... we had an incorrect security setting on our database so your original solution worked.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20049634
Glad to be of help :o)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question