CODEWITHSUNDEEP
sqlms-access
rsrobbins
rsrobbins

Reputation: 629

Update A multi-valued field in Access

I have created a lookup table in Access to provide the possible values for a column. Now I need to update this column with the data it had before I converted the column. I am unable to figure out a SQL Query that will work. I keep getting the error "An UPDATE or DELETE query cannot contain a multi-valued field." My research has suggested that I just need to set the value of the column but this always updates 0 records:

UPDATE [table_name] SET [column_name].Value = 55 WHERE [table_name].ID = 16;

I know this query will work if I change it to update a text column, so it is definitely a problem with just this column.

Upvotes: 10

Views: 25150

Answers (7)

HansUp
HansUp

Reputation: 97101

If you're adding a value to your multi-valued field, use an append query.

INSERT INTO table_name( [column_name].Value )
VALUES (55)
WHERE ID = 16;

If you want to change one particular value which exists in your multi-valued field, use an UPDATE statement. For example, to change the 55 to 56 ...

UPDATE [table_name]
SET [column_name].Value = 56
WHERE [column_name].Value = 55 And ID = 16;

See Using multivalued fields in queries for more information.

Upvotes: 17

Marco
Marco

Reputation: 57583

I gotta say I didn't understand very well your problem but I saw something strange in your query. Try this:

UPDATE [table_name] SET [column_name]= 55 WHERE [table_name].ID = 16;

UPDATE:
Look at this link: it has an example

UPDATE Issues 
SET Issues.AssignedTo.Value = 10
WHERE (((Issues.AssignedTo.Value)=6) 
AND ((Issues.ID)=8));

NOTES

You should always include a WHERE clause that identifies only the records that you want to update. Otherwise, you will update records that you did not intend to change. An Update query that does not contain a WHERE clause changes every row in the table. You can specify one value to change.

Upvotes: 0

Fandango68
Fandango68

Reputation: 4868

The Multi-Valued field refers to Access databases that have tables with columns, that allow you to select multiple values, like a Combo Checkbox list.

THOSE are the only Access types that SQL cannot work with. I've tested all Access lookup possibilities, including hard-coded values, and lookup tables. They work fine, but if you have a column that has the Allow Multiple select options, you're out of luck. Even using the INSERT INTO as mentioned below, will not work as you'll get a similar but different error, about INSERTing into multi-valued fields.

As mentioned it's best to avoid using such tables outside of Access, and refer to a table specifically for your external needs. Then write a macro/vba script to update the real tables with the data from the "auxiliary" table.

Upvotes: -2

Don
Don

Reputation: 9

INSERT INTO Quals (cTypes.[value])
SELECT Quals_ContractTypes.ContractType
FROM Quals_ContractTypes
WHERE (Quals.ID = Quals_ContractTypes.ID_Quals);

Upvotes: 0

Tonig
Tonig

Reputation: 31

I am working with Sharepoint, I created the tables as multi-value fields, ran into the error with my INSERT INTO statement, went back to Sharepoint to change to non-multi-value fields, but that didn't fix it.

Recreated the table without using multi-value fields, and the INSERT INTO worked just fine.

Upvotes: 3

rsrobbins
rsrobbins

Reputation: 629

I have figured this out! It certainly was counter-intuitive! You have to use an INSERT statement to do the update.

-- Update a record with a multi-valued field that has no value
INSERT INTO [table_name] ( [[column_name].[Value] )
VALUES(55)
WHERE [table_name].ID = 16;

This confused me because I was expecting an UPDATE statement. I think it actually inserts a record into a hidden table that is used to associate multiple values with this column.

Upvotes: 5

Saic Siquot
Saic Siquot

Reputation: 6513

do not use the .value part

UPDATE [table_name] SET [column_name] = 55 WHERE [table_name].ID = 16;

Upvotes: 0

Related Questions