Solved

simple Update query

Posted on 2006-07-06
11
285 Views
Last Modified: 2008-02-01
Sorry Brain freeze - Help with simple Update query

Easy 500 Points for the right answer.

I always get this confused.

I want to update the Cardtype in table 1 with the value of Cardtype in table2 where the month =0 and the regions match.

What is the correct syntax.

thanks,

Karen
0
Comment
Question by:Karen Schaefer
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 250 total points
ID: 17055160
UPDATE table1 INNER JOIN table2 ON table1.region = table2.region  SET table1.cardtype = table2.cardtype
WHERE (((table2.month)="0"));
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17055183
?
UPDATE Table1 INNER JOIN Table2 ON Table1.Region = Table2.Region SET Table1.CardType = Table2.CardType WHERE (((Table2.month)=0));
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17055185
sorry Ray...I'm too slow!
:o)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Karen Schaefer
ID: 17055192
Sorry I always get mixed up which table is table1 the table the data is being updated or the table where the data is coming from?

K
0
 

Author Comment

by:Karen Schaefer
ID: 17055199
My tables names are

UMA_SUBS.cardtype data I want to update
UMAPERF_MN.WSSCARDTYPE where the data is coming from.

Thanks,

K
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17055207
UMA_SUBS.cardtype data I want to update - table 1
UMAPERF_MN.WSSCARDTYPE where the data is coming from. - table 2 (as this has the where statement)
0
 

Author Comment

by:Karen Schaefer
ID: 17055240
M_region       M_Node        Load Type      Month      UMA_Subs      CardType
Atlanta       ATMSS995       MSS/UNC      0      3554      
Chicago       CHMSS965       MSS/UNC      0      3554      
Seattle       SEMSS994       MSS/UNC      0      3554      
Houston       HNMSS983       MSS/UNC      0      53554      
Orlando       ORMSS963      MSS/UNC      0      153554      
Denver       DNMSS935       MSS/UNC      0      253554      
Detroit       DEMSS931       MSS/UNC      0      153554      
Dallas       DAMSS986       MSS/UNC      0      353554      
Los Angeles      IRMSS002      MSS/UNC      0      553554      

UMA_Subs Table Data

I want to update the Cardtype in UMA_Subs where the Month = 0  and the M_Region = UMAPERF_MN.M_Region
withh the data from UMAPERF_MN.WSSCARDTYPE

K
0
 
LVL 8

Accepted Solution

by:
infolurk earned 250 total points
ID: 17055261
Usint Raynards query and your info;
UPDATE UMA_Subs INNER JOIN UMAPERF_MN ON UMA_Subs.M_Region = UMAPERF_MN.M_Region  SET UMA_SUBS.cardtype = UMAPERF_MN.WSSCARDTYPE
WHERE (((UMA_Subs.month)="0"));

Cheers
Steve
0
 

Author Comment

by:Karen Schaefer
ID: 17055265
Thanks thats great - you both can share the points.

Karen
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17055266
update
  UMA_Subs inner join UMAPERF_MN on UMA_Subs.M_Region = UMAPERF_MN.M_Region SET UMA_Subs.Cardtype = UMAPERF_MN.Cardtype Where UMAPERF_MN.Month = 0
0
 
LVL 8

Expert Comment

by:infolurk
ID: 17055318
Cheers.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

803 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