473,836 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL UPDATE INNER JOIN syntax

91 New Member
I have the following syntax for Update Inner Join:

Expand|Select|Wrap|Line Numbers
  1. UPDATE RuleDetail 
  2.  
  3. SET RuleDetail.Function_involved = MasterUpload.Function_involved 
  4.  
  5. FROM RuleDetail
  6.  
  7. INNER JOIN MasterUpload 
  8.  
  9. ON RuleDetail.Rule_description = MasterUpload.Rule_description
However, when I am trying to run it, I am getting an error message -

"Syntax error (missing operator)in query expression 'MasterUpload.F unction_involve d

FROM RuleDetail

INNER JOIN MasterUpload

ON RuleDetail.Rule _description = MasterUpload.Ru le_description' ."

Could you please advise where I am going wrong here?

Thanks,
SG
Mar 21 '12 #1
20 19502
MikeTheBike
639 Recognized Expert Contributor
Hi

I'm not all together sure but I tyhink it should something lie this
Expand|Select|Wrap|Line Numbers
  1. Update MasterUpload INNER Join RuleDetail
  2. ON RuleDetail.Rule_description = MasterUpload.Rule_description
  3. Set RuleDetail.Function_involved = MasterUpload.Function_involved
??

MTB
Mar 21 '12 #2
sg2808
91 New Member
To run the above syntax, I created a copy of the RuleDetail table (to RuleDetail1) to check if it is working properly. I also modified the query (to RuleDetail1).

On hitting Run, I am getting the following error message:

Cannot join on Memo, OLE, or Hyperlink Object (Ruledetail1.Ru le_description = MasterUpload.Ru le_description) .

So, it is still not working.
Mar 21 '12 #3
Rabbit
12,516 Recognized Expert Moderator MVP
That's because you can't join on the data types Memo, OLE, or Hyperlink. I assume you're using a Memo data type for your description. You should never use memo unless you actually need that many characters. Use text whenever possible.
Mar 21 '12 #4
sg2808
91 New Member
See the SQL code below for an update query using inner join.

Expand|Select|Wrap|Line Numbers
  1. UPDATE Risk1.ProcessID
  2. SET Risk1.ProcessID=Process.ProcessID
  3. From masterdata INNER JOIN Process
  4. ON Masterdata.Detail_process_name=Process.Detail_process_name
I have tried what @Mike the bike had suggested but this is still not working.
I am getting the same error:

Syntax error (missing operator)in query expression 'Process.Proces sID From MasterdataINNER JOIN Process ON
Masterdata.Deta il_process_name =Process.Detail _process_name


Please help/advise.
Mar 22 '12 #5
NeoPa
32,584 Recognized Expert Moderator MVP
You're not paying attention and progressing in order.

MTB has already fixed one issue for you (If you're unsure of the correct syntax then check it out in Help - Finding Jet SQL Help).

This raised another which Rabbit explained. Your response seems to ignore Rabbit's comment. Furthermore it changes the SQL drastically such that it no longer uses either of the record sources found in post #1. That is irrational behaviour and will result in nothing but grief for you.

I suggest you post something that clarifies :
  1. Which tables/queries you're actually working with.
  2. The field types of each of the fields used in any JOIN statements.

With this information we might be able to proceed.
Mar 22 '12 #6
sg2808
91 New Member
Sorry NeoPa but I am not ignoring any of the helps above. As @Rabbit pointed out, I have changed my data so as not to include a Memo field.

Let me start afresh.

I am trying to update a column (ProcessID) in a table(Risk1) from another table (Process) which is having the ProcessID data.

Please note that both the tables (Process and Risk1) have more columns which are already populated.

The data of the above two tables are also in a single master table(Masterdat a).

I am trying to pull out the data form the ProcessID field from table-Process (Process.Proces sID) and use the INNER JOIN with the table-Masterdata and update the ProcessID column in table-Risk1 (Risk1.ProcessI D).

When I am running the above query, it is giving me an error as mentioned above.

Again, The tables are - Process, Risk1 and Masterdata

The fields are - ProcessID(in tbl Process), ProcessID (in Risk1), Detail_process_ name(in Process) and Detail_process_ name(in Masterdata)

The process ID fields are Number type and Detail_process_ name is text type.

Additional comments - The master table is the source of all data and I know from this table the relationship between the Process and Risk1.
The data is split into two tables - Process and Risk1. The PK of Process is the FK in Risk1. I am trying to populate the FK column in Risk1 table and hence the query.
Mar 23 '12 #7
NeoPa
32,584 Recognized Expert Moderator MVP
sg2808:
Sorry NeoPa but I am not ignoring any of the helps above. As @Rabbit pointed out, I have changed my data so as not to include a Memo field.
I'm pleased to hear it, but making such a change without explaining that that is what you've done, is not a helpful way to proceed, as I'm sure you must now realise.

Somewhere along the line you changed the SQL from one invalid syntax to something else (equally invalid), and the format that MTB posted doesn't seem to have registered with you at all.

sg2808:
The data of the above two tables are also in a single master table(Masterdat a).
I assume you mean that some sort of copy of the data is contained in [MasterData]? I have no idea why you are complicating matters with this table if what you said about what you're trying to do is correct. It seems to have no bearing on the matter at all and is obviously confusing you badly.

Unfortunately, as your explanation doesn't seem to add up to a consistent whole I will have to guess here which bits I can rely on and which bits are a result of expressing your situation from a confused position. If I get it wrong you may need to clarify your explanation (You appreciate it's hard to give a correct answer when the question itself is so wrong). So, I will assume that [MasterData] is a complete irrelevance and work with :
sg2808:
I am trying to update a column (ProcessID) in a table(Risk1) from another table (Process) which is having the ProcessID data.
Try this :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Risk1] INNER JOIN [Process]
  2. ON     Risk1.Detail_process_name=Process.Detail_process_name
  3. SET    Risk1.ProcessID=Process.ProcessID
As I say, if that isn't your problem then we need something clear from you that makes sense.
Mar 23 '12 #8
sg2808
91 New Member
Thanks NeoPa.

The problem here is that the Risk1 table do not include the Detail_process_ name field. Infact, there is no common field between the two tables (Process and Risk1).

This field (Detail_process _name) is in the Masterdata table and Process table. I was trying to use these tables to update the Risk1 field (it may not be the right way).

The Masterdata table (that has all the fields which are in Process and Risk1 tables) is not relevant for my database but I have this table in the database to help me to update the FK field (ProcessID) in Risk1.

I may be wrong in my thinking but cant think of any other approach. May be you can help me with the approach or the syntax.
Mar 23 '12 #9
sg2808
91 New Member
NeoPa,

For further clarification, I am continuing here as there is link between the posts.

I was trying to use the logic as per your solution (see Post#21) for the question I had on the link below.

http://bytes.com/topic/access/answer...er-import-data

However, in this case, I am trying to update a specific column only.
Mar 23 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2747
by: Richard | last post by:
Hi, I work on a VB6 software using ADO to connect to Oracle 9.2i. When I create a recordset based on a query containing a "inner join" clause (sql server syntax, the software connects also to sql server and the "inner join" syntax must be preserved for a large fraction of the queries), AddNew and Update methods fail.
3
59085
by: indabert | last post by:
Hello, I have two tables (table1 and table2). I want to set a flag in table1 for each common row with table2. I use the following syntax UPDATE table1 SET flag='Y' from table1 INNER JOIN table2 on (table1.a = table2.a) AND (table1.b = table2.b) However the situation arises where I may have a row in table2 that matches with two or more rows in table1. The requirement is that I only want to set the flag on a single row in table1.
4
12584
by: meyvn77 | last post by:
Im using an ADP to connect to a SQL Sqever DB. In access it was really easy to say Inner join on table1 and table2 and update columnA from table1 with columnC from table2 where table1.key = table2.key and table2 columnB = 1 and table2 columnD = 4 I have tried all manner of beasts to get this thing to work..
10
10545
by: deko | last post by:
I've tried each of the below, but no luck. UPDATE tblEntity As tbl INNER JOIN search3220 As qry ON tbl.Entity_ID = qry.Entity_ID SET tbl.Cat_ID = 289; UPDATE tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID = seach3220.Entity_ID SET tblEntity.Cat_ID = 289; The only issue I can imagine aside from syntax is that Entity_ID is not unique in the query. tblEntity will only have one Entity_ID of 1700, for
6
9323
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: tblEmployees empId -- EmpName -- EmpRole -- EmpManager -------....------------.... ---------....--------------- 1........ dan yella..........1..........2
52
6368
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
12
3940
by: info | last post by:
The following query works fine in access and does exactly what I want it to do however I get a syntax error when I port it over to SQL Server 2000. ------------- UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID = Products.ProductID SET Products.Stock = Products.Stock-OrdersProducts.Quantity WHERE OrdersProducts.OrderID=63116
12
13193
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
11
19974
by: YZXIA | last post by:
Is there any difference between explicit inner join and implicit inner join Example of an explicit inner join: SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID
1
2617
maxamis4
by: maxamis4 | last post by:
I have the following query that I am trying to carry over from ms access to ms sql server. I keep getting a msg 156, level 15, state 1, line 2 Incorrect syntax near the keyword "INNER". it works in ms access and I am not sure why its not working on this side. UPDATE dbo.cr_stat INNER JOIN (dbo.call_req INNER JOIN dbo.z_tbl_D_26_Time_To_Resolve_CR ON dbo.call_req.persid = dbo.z_tbl_D_26_Time_To_Resolve_CR.CR_Persid) ON ...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10541
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10584
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10248
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7782
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.