?
Solved

Update Query - Need Help

Posted on 2006-06-07
8
Medium Priority
?
223 Views
Last Modified: 2008-03-06
Hello,

I have two tables with the same fields.  However, in the first one, the older one, some fields have data that the new table does not.  I want to get the data in those fields over into the newer table where the lob is equal.  For instance, in the older table, there is a field called CIO that lists the CIOs for the LOB they represent.  In the newer table however, I have the LOB but not the associated CIO.  How can I link on LOB and get the CIO from the older table into the newer one?

Thanks,
John
0
Comment
Question by:jz1english
  • 5
  • 3
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16853076
something like this perhaps


update newtable, oldtable
set newtable.cio = oldtable.cio
where newtable.lob = oldtable.log


usual thing is to backup db first
0
 

Author Comment

by:jz1english
ID: 16853222
thanks but I don't know sql so i need to know how to enter this int he query editory
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16853450
what is the exact tablenames and fieldnames? u need to replace what I have done with yours

In MS Access, if u go into Query Editor

add a new query, just write the above but substituting your tablenames/fieldnames
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 16853533
here is some tutorials
http://www.fgcu.edu/support/office2000/access/

What u need to do with u go into a query, is to change from design view to sql view
then do it that way

0
 

Author Comment

by:jz1english
ID: 16853675
this is what i put in the sql editor but i got an error:

update open_remediation_report_source_data, open_remediation_report_source_data_update
set open_remediation_report_source_data_update.cio = open_remediation_report_source_data.cio
where open_remediation_report_source_data.Eval Name = open_remediation_report_source_data_update.Eval Name

the error says:

syntax error in query expression: where open_remediation_report_source_data.Eval Name = open_remediation_report_source_data_update.Eval Name
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16853888
if u have spaces in your tablenames or fieldnames, u have to put them in sqaure brackets
e.g.


update open_remediation_report_source_data, open_remediation_report_source_data_update
set open_remediation_report_source_data_update.cio = open_remediation_report_source_data.cio
where open_remediation_report_source_data.[Eval Name] = open_remediation_report_source_data_update.[Eval Name]


did u backup your db first?
0
 

Author Comment

by:jz1english
ID: 16860377
hey rockiroads,

i had to leave the office early yesterday.  i will get in in about an hour and try this out.  let you know how it goes.  thanks....no, i did not back up the db.  no biggie though, if it fails, i can just reimport those two table without a problem.  thanks.......
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16860472
ok, no worries
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

807 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