MS Access SQL: Replacing a field with a string + another row value

Question

I am trying to update a table in MS Access database which contains some movie information

    [Table:Movies]
    MovieName         CrewId      CrewMember
    The Big Lebowski        1       Joel Coen
    The Big Lebowski        2       Ethel Coen
    The Big Lebowski        3       Carter Burwell
    The Big Lebowski        4       Roger Deakins
    The Matrix              1       Andy Wachowski
    The Matrix              2       Lana Wachowski
    The Matrix              3       Don Davis
    The Matrix              4       Bill Pope

CrewId 1 is director and 2 is co/assistant director, and so on.

What i am trying to do is replace co-director name in 'CrewMember' column with "Assistant of Director Name", like below

    [Table:Movies]

    MovieName         CrewId      CrewMember
    The Big Lebowski        1       Joel Coen
    The Big Lebowski        2       Assistant of Joel Coen
    The Big Lebowski        3       Carter Burwell
    The Big Lebowski        4       Roger Deakins
    The Matrix              1       Andy Wachowski
    The Matrix              2       Assistant of Andy Wachowski
    The Matrix              3       Don Davis
    The Matrix              4       Bill Pope

I am using the following query which is giving Syntax error (missing operator).

    UPDATE t1
    SET t1.CrewMember = 'Assistant of '+ t2.CrewMember
    FROM Movies t1, Movies t2
    WHERE t1.MovieName = t2.MovieName
    AND t1.CrewId = 2
    AND t2.CrewId = 1;

Please help me with this query

After you replace 'ethel cohen' and 'lana wachoswki', do you plan to keep their names around anywhere? — PowerUser
– PowerUser, Commented Jul 11, 2013 at 21:01
No. That is to be replaced by the new string. btw Fabians solution worked. Thanks guys. — JSmith
– JSmith, Commented Jul 11, 2013 at 21:17

Fabien TheSolution · Accepted Answer · 2013-07-11 20:52:51Z

2

Try this :

UPDATE Movies as t1, Movies as t2
SET t1.CrewMember = 
'Assistant of ' + t2.CrewMember
WHERE t1.MovieName=t2.MovieName AND t1.CrewId=2 AND t2.CrewId=1

answered Jul 11, 2013 at 20:52

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JSmith Over a year ago

Thanks a lot Fabian. I still don't know what i was doing wrong but your solution worked like a charm.

Fabien TheSolution Over a year ago

There is no FROM clause in the UPDATE syntax, you need to put both table after the UPDATE msdn.microsoft.com/en-us/library/office/…

EkoostikMartin · Accepted Answer · 2013-07-11 20:32:50Z

1

In Access, string concatonation is done using "&"

So it should be:

UPDATE t1
    SET t1.CrewMember = 'Assistant of '& t2.CrewMember
    FROM Movies t1, Movies t2
    WHERE t1.MovieName = t2.MovieName
    AND t1.CrewId = 2
    AND t2.CrewId = 1;

answered Jul 11, 2013 at 20:32

EkoostikMartin

6,9313 gold badges35 silver badges63 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-07-11 20:34:55Z

0

I am guessing the reason is because the string concatenation operator in Access is &, not +. Also, I think Access requires the as for table aliases, and you can use a join for the condition:

UPDATE t1
    SET t1.CrewMember = 'Assistant of '+ t2.CrewMember
    FROM Movies as t1 join
         Movies as t2
         on t1.MovieName = t2.MovieName
    where t1.CrewId = 2 AND t2.CrewId = 1;

answered Jul 11, 2013 at 20:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

JSmith Over a year ago

thanks for your response. I tried everything you suggested first individually and then at the same time but i'm still getting the same error. ` Syntax error (missing operator) in query expression "Assistant of' & t2.CrewMember FROM Movies t1 JOIN Movies as t2 ON t1.MovieName = t2.MovieName'. ` and it highlights FROM in the query after dismissing error dialog.

PowerUser Over a year ago

@JSmith, in your error message, it looks like you used both a double and a single quote. While you can use one or the other, you just can't use them interchangably in the same statement. Try just one or the other, i.e. "Assistant of" &

JSmith Over a year ago

i noticed that and re checked my query. I have used single quotes in my query for the string. The opening double quote appears only in the error message. Its strange.

Collectives™ on Stack Overflow

MS Access SQL: Replacing a field with a string + another row value

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related