CODEWITHSUNDEEP
sqlms-access
JSmith
JSmith

Reputation: 45

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

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

Upvotes: 1

Views: 1444

Answers (3)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 0

EkoostikMartin
EkoostikMartin

Reputation: 6921

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;

Upvotes: 1

Related Questions