SQL UPDATE INNER JOIN syntax

[sg2808]

I have the following syntax for Update Inner Join:

UPDATE RuleDetail 
 
SET RuleDetail.Function_involved = MasterUpload.Function_involved 
 
FROM RuleDetail
 
INNER JOIN MasterUpload 
 
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.Function_involved

FROM RuleDetail

INNER JOIN MasterUpload

ON RuleDetail.Rule_description = MasterUpload.Rule_description'."

Could you please advise where I am going wrong here?

Thanks,
SG

[MikeTheBike]

Hi

I'm not all together sure but I tyhink it should something lie this

Update MasterUpload INNER Join RuleDetail
ON RuleDetail.Rule_description = MasterUpload.Rule_description
Set RuleDetail.Function_involved = MasterUpload.Function_involved

??

MTB

[sg2808]

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.Rule_description = MasterUpload.Rule_description).

So, it is still not working.

[Rabbit]

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.

[sg2808]

See the SQL code below for an update query using inner join.

UPDATE Risk1.ProcessID
SET Risk1.ProcessID=Process.ProcessID
From masterdata INNER JOIN Process
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.ProcessID From MasterdataINNER JOIN Process ON
Masterdata.Detail_process_name=Process.Detail_proc ess_name

Please help/advise.

[NeoPa]

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.

[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.

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(Masterdata).

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

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.

[NeoPa]

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(Masterdata).

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 :

UPDATE [Risk1] INNER JOIN [Process]
ON     Risk1.Detail_process_name=Process.Detail_process_name
SET    Risk1.ProcessID=Process.ProcessID

As I say, if that isn't your problem then we need something clear from you that makes sense.

[sg2808]

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.

[sg2808]

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.

[Rabbit]

I too am confused as to why you're keeping two copies of the same data.

This is the first I'm hearing about three tables. You never mentioned that originally. Also, your table names and field names keep changing from each attempt. It's now at the point where I have no idea which table and field name is correct.

If we make the assumption that post 7 is actually what you have, and you have three tables to join together, then you have to join those three tables together.

You say that you're following Mike's example but all your SQL attempts look nothing like his example. You need to take a closer look at his example.

[NeoPa]

It seems you have a problem explaining your questions very clearly (The other thread has that same problem too). Let's see what we can do to help with that then (as this is still like trying to swim through treacle).

Please post, in here, all the fields that you think are relevant from all the tables that you think are, or should be, involved in this problem. Please make clear the actual names of each table and field (Using [] around actual names makes it clear you're referring to actual object names). For each field, please identify which fields in any of the other relevant tables it can be used to link from or to.

Identify also, which field is to be updated, and which field the data is to be taken from. If you do all this in this single post then finding relevant info won't be such a chore. I will continue on the basis that this new post is absolutely correct and not the information from any previous posts. It is very important therefore, that you get this right first time. If not, we go back to a situation where there is much information in many posts and knowing what we can rely on and what we cannot is a struggle every time we post.

[sg2808]

Yes, post 7 is actually what I have.

I do not want to keep the Masterdata table, I would be deleting it. I have brought it in to help me use the INNER JOIN between table-Process and table-Masterdata.

NeoPa's SQL is definitly different, I am just trying to use the logic (as his SQL also involves 3 tables). His SQL is Inserting the data in a 3rd table using inner join in other two tables.

Similarly, I am tying is to Update the Process ID field in Risk1 table using INNER JOIN on Process and Masterdata table.

My approach or the problem could be totally wrong and I need something more to be done but this is where I need your expert opinion.

[Rabbit]

NeoPa's SQL is different from Mike's only in the table names and field names. The logic remained the same. Neo does not use three tables. In essence, Neo's SQL is the same as Mike's.

[NeoPa]

sg2808:
I need something more to be done but this is where I need your expert opinion.

Not until you've responded intelligently to post #12. Without that information (which, as I'm sure you're beginning to understand, should have been included in the first post with the question) we are in no position to understand what you are continuing to talk about.

[sg2808]

The tables are - [Process], [Risk1] and [Masterdata]

The fields (format is [tablename].[fieldname]) relevant for this discussion, I think, are:

[Process].[ProcessID]- PK
[Process].[Detail_process_name]
[Risk1].[RiskID]-PK
[Risk1].[ProcessID]-FK
[Risk1].[Risk_ref_no]
[Masterdata].[MasterdataID]-PK
[Masterdata].[Detail_process_name]
[Masterdata].[Risk_ref_no]

The data under [Process].[ProcessID] needs to be taken out.
The field [Risk1].[ProcessID] needs to be updated with the above data.

Table[Masterdata] can be linked to table[Process] on field[Detail_process_name].

Table[Masterdata] can be linked to table[Risk1] on field [Risk_ref_no].

[sg2808]

@Rabbit - That was my first stab at SQL and NeoPa's SQL is printed and pinned on my desk for easy reference and inspiration :-)

He has for sure mentioned three tables in his SQL - tI, tHL and tblDetail.
The data selected from tI and tHL is Inserted in tblDetail and this is what I had mentioned in the previous post.

[NeoPa]

sg2808:
He has for sure mentioned three tables in his SQL - tI, tHL and tblDetail.
The data selected from tI and tHL is Inserted in tblDetail and this is what I had mentioned in the previous post.

Lol. Rabbit was, quite sensibly, referring to the contents of this thread SG. When he said "NeoPa's SQL is different from Mike's only in the table names and field names", he meant that the SQL from posts #2 and #8 were very similar (identical) in structure and syntax.

If he were referring to SQL from other threads, even the one linked in your post #10, he would be clever enough to make that clear in his statement.

Post #16. This is exactly what was required (If I were being extremely picky I would point out that you've only provided one field name for each link, but as it seems obvious that the linking is done between fields of the same names in your case that would be ultra picky even for me).

Try out this SQL and see what you get :

UPDATE ([MasterData]
       INNER JOIN
       [Process]
    ON MasterData.Detail_Process_Name = Process.Detail_Process_Name)
       INNER JOIN
       [Risk1]
    ON MasterData.Risk_ref_no = Risk1.Risk_ref_no
SET    Process.ProcessID = Risk1.ProcessID

[sg2808]

Yep, realized after posting that :-), never mind.

[sg2808]

Hi NeoPa,

When I tried to run with your SQL, I get a message as - "Cannot update Process.ProcessID; field not updateable. I guess, because Process.ProcessID field is a autonumber field.

Looking at the SQL above, I just want to cross check that the last line, should it be written as (because I would like to update the Risk1.ProcessID field):

SET Risk1.ProcessID = Process.ProcessID

When I run the SQL with the change as above, I get this message - "You are about to update 0 row(s)....".

[NeoPa]

sg2808:
I guess, because Process.ProcessID field is a autonumber field.

You can believe it. If, as you say, Process.ProcessID is an AutoNumber field, then it certainly could not be updated.

sg2808:
When I run the SQL with the change as above, I get this message - "You are about to update 0 row(s)....".

The SQL is fine, but I suspect there is something else awry with what you've told us. Recent posts have started to indicate a consistent situation that makes sense. Unfortunately, this indicates it's not a completely accurate representation of your real position. SOmething is not right, but I'm afraid I can't tell you what that is as I don't have the information from you with which to determine what that might be. All I can say is that there appear to be no records selected which match the criteria specified. Hopefully, that will be enough to get you looking in the right direction at least.