Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Update query check

Posted on 2014-04-23
4
Medium Priority
?
263 Views
Last Modified: 2014-04-23
I am going about this the other way.  Please tell me EXACTLY what the SQL below will do.  This is a check before I push the button and jump into disaster (hopefully not).  That's Mr. Trebek to you.

INSERT INTO dbo_Mfg_Part_Master ( UDFKEY_49 )
SELECT qryExistingMatchingMPNs2.WW_PN
FROM dbo_Mfg_Part_Master INNER JOIN qryExistingMatchingMPNs2 ON (dbo_Mfg_Part_Master.MPNNUM_49 = qryExistingMatchingMPNs2.MPNNUM_49) AND (dbo_Mfg_Part_Master.PRTNUM_49 = qryExistingMatchingMPNs2.PRTNUM_49);

TIA,

Tim
0
Comment
Question by:gibneyt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 400 total points
ID: 40018290
For starters, ALWAYS back up the database before running an untested action query.

The query selects WW_PN from a query where MPNNUM_49 from the part master = MPNNUM_49 from the query and PRTNUM_49 from the part master = PRTNUM_49 from the query, and adds new rows to dbo_Mfg_Part_Master with WW_PN as UDFKEY_49

Are you sure you want to add new rows?  Perhaps this is supposed to be an update query?  Update queries modify the values of columns in EXISTING rows and Insert/appent queries, add NEW rows.
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 300 total points
ID: 40018291
Rather than hit the "Execute" (red exclamation) button, if you hit the datasetview button, it'll show you exactly what'll be updated without performing the update..... then go back to the designer/sql view and make necessary changes.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
ID: 40018292
Executing the above query will cause the world to end.  Don't do it.

Just kidding.  Executing the above query will insert into table dbo_Mfg_Part_Master, column UDFKEY_49, the results of the SELECT statement (i.e. everything from SELECT on down).
0
 

Author Closing Comment

by:gibneyt
ID: 40018492
WOW!  All within the first 7 minutes!

Yes, backed up of course and done after hours while no one is accessing the database except me.  And possibly even using a test DB first.

I think this is the literal translation of the SQL above:

Update the field dbo_Mfg_Part_master.UDFKEY_49 with the contents of qryExistingMatchingMPNs2.WW_PN where qryExistingMatchingMPNs2.MPNMUN_49 equals dbo_Mfg_Part_Master.MPNNUM_49 AND qryExistingMatchingMPNs2.PRTMUN_49 equals dbo_Mfg_Part_Master.PRTNUM_49

Agreed?

Yes, Datasheet View shows me the contents of WW_PN in a single column.

Thanks Gents,

Tim
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

618 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