CODEWITHSUNDEEP
sqlms-access
Khaled
Khaled

Reputation: 13

Access SQL statements filling column with values from another table

I have 2 tables: Table called "Join_of_LIN_and_LI2" having 2 columns as follows:

Fcode   |   Description
103     |
4301    |
5200    |

and the second table called "Diam"having 2 columns as follows:

Fcode   |   Description
5200    |   S Force Line

I want to fill the field "Description" of the table "Join_of_LIN_and_LI2" with the values of the field "Description" of the table "Diam" when the field "Fcode" of table "Join_of_LIN_and_LI2" is equal to the field "Fcode" of table "Diam".

What SQL statement I should use? I tried the below but doesn't work

UPDATE Join_of_LIN_and_LI2
SET Description = (SELECT Diam.Description
                   FROM Join_of_LIN_and_LI2
                   LEFT JOIN Diam ON Join_of_LIN_and_LI2.Fcode = Diam.Fcode;)

It gives an error "Operation must use an updateable query" although I am sure that the user has the permissions to write on that file.

Thanks

Upvotes: 0

Views: 765

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a subquery, but it should be a correlated subquery:

UPDATE Join_of_LIN_and_LI2
    SET Description = (SELECT Diam.Description
                       FROM Diam 
                       WHERE Join_of_LIN_and_LI2.Fcode = Diam.Fcode
                      );

Note: This is different from the JOIN in two ways. First, this will set Description to NULL when there are no matches. The JOIN version will not change the existing value. Second, if there are multiple matches in Diam, then this returns a "subquery returns more than one row" type of error.

Upvotes: 1

Erik A
Erik A

Reputation: 32632

You don't need to use a subquery for this. You can just use a join in the UPDATE query:

UPDATE Join_of_LIN_and_LI2 
    INNER JOIN Diam 
    ON Join_of_LIN_and_LI2.Fcode = Diam.Fcode
SET Join_of_LIN_and_LI2.Description = Diam.Description;

The Operation must use an updateable query refers to the fact that the query is not updateable. That can happen for many reasons, but in this case, it's because you're using a subquery.

Upvotes: 2

Related Questions