NEED HELP WITH 'operation must use updateable query'

[jker]

I have 2 tables. I want to update the EXPLODE_CUST_NAME with the contents of CUST_FNAME from CUSTOMER table.


the EXPLODE_CUSTOMER_ID value is used to match/select the corresponding CUST_ID and pull out the CUST_FNAME contents

BUT I KEEP GETTING that error WHEN I RUN MY QUERY .

here is my query sql :

UPDATE SALES_HISTORY_EXPLODED AS X
SET X.EXPLODE_CUST_NAME = (SELECT C.CUST_FNAME FROM CUSTOMER AS C
WHERE X.EXPLODE_CUSTOMER_ID = C.CUST_ID)

[June7]

Why are you saving customer name in exploded table when the name can be retrieved by joining tables? At least, you could join if the CUSTOMER_ID foreign key was a long integer number type field.

Should explode_ID be an autonumber type?

Duplicating data between multiple tables is usually bad design.

Why is your query not updatable? Review sixth item in http://allenbrowne.com/ser-61.html#:...FORM%20clause.

[jker]

THANK YOU FOR YOU ADVICE, BUT THESE ARE THE BURDENS WHEN YOU INHERIT A SYSTEM AND NEED TO MAKE 'minor' modifications as per user request.

neither table has primary keys by design.... I looked at teh document you sent and none of them apply.. is there anyother way to to this 'simple' task?

[Micron]

For your information, using all caps in a sentence is considered to be shouting and rude.
It is, as pointed out to you, caused by item #6.
If the text id's happen to be the auto id numbers then I'd consider changing the data type to long and just joining the tables. Otherwise I'd fix the table design because it's incorrect. There is no way you're going to update any table when the query contains a subquery.

[CJ_London]

neither table has primary keys by design....

So what do you think the key symbol means?


And why do you think you can match a number field to a text field?




EXPLODE_CUSTOMER_ID = C.CUST_ID

[jker]

Relax....... i have a broken hand in a cast... Caps werent intentional...... here is a lolly pop if you felt slighted

[orange]

Relax yourself. People here are experienced volunteers. We see all sorts of questions and comments. Most, if not all of us, have had to take over an application without documentation, without a sponsor and have been "thrown into a situation(s)" as you have.

We see poor designs quite often. Comments on poor design are not a reflection of the poster necessarily- your case is an example. Comments are meant as facts or norms (constructive criticism). Poor design can often be identified and corrected, and that is what the advice given by the others intended.

If your CaPs were not intentional, then you have excellent coordination (or an AI driven contextual keyboard) providing the appropriate mechanics of writing displayed in the upper and lower case in your post #1.

There is no need to be snarky. We're trying to be helpful.

[Micron]

Some people are not cognizant of proper forum decorum. Some are just rude. You are at least the latter if not both. Don't worry, you can have the last word on this. I won't see it because I'm unsubscribing from this thread and putting you on my ignore list.