MS Access update query and inner join with string parameter

Question

Please help me solve this problem in MS Access. I want to update my user table using the statusid that I will get in userstatus table.

This query properly work in MySQL. Why in MS Access not. Inner join with a parameter of string doesn't work.

UPDATE tbl_users AS us
  INNER JOIN tbl_userstatus AS ust ON ust.status = "Offline"  
SET us.statusid =  ust.statusid 
WHERE us.username = "francis";

the joining key is the statusid. but I want to get the statusid from my table tbl_userstatus using the parameter 'Offline'. — Fluffy Potato
– Fluffy Potato, Commented Oct 7, 2018 at 11:17

Gordon Linoff · Accepted Answer · 2018-10-07 11:37:37Z

3

In MS Access, you can try:

UPDATE tbl_users AS us,   -- MS Access does not support explicit CROSS JOIN
       tbl_userstatus AS ust
    SET us.statusid =  ust.statusid 
    WHERE us.username = "francis" AND ust.status = "Offline";

In either database, you can use:

UPDATE tbl_users
    SET statusid =  (SELECT ust.statusid FROM tbl_userstatus AS ust ON ust.status = "Offline")
    WHERE username = "francis";

This are not exactly the same. But I assume that tbl_userstatus has exactly one row with "Offline" -- in which case they are equivalent.

answered Oct 7, 2018 at 11:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-07 07:49:43Z

0

Try like below, assume statusid is the joining column between two tables and guess that you want to update status column of user table by using data tbl_userstatus of column status

UPDATE us
SET    us.status =  ust.status 
FROM   tbl_users AS us
       INNER JOIN tbl_userstatus  AS ust  
              on us.statusid =  ust.statusid
         WHERE us.username = "francis"

answered Oct 7, 2018 at 7:49

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

1 Comment

Fluffy Potato Over a year ago

What I want is to use a string in an Inner Join statement. Something like this Inner Join table1 ON table1.column1 = 'myString'. MS Access doesn't want this kind of query.

Collectives™ on Stack Overflow

MS Access update query and inner join with string parameter

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related