1

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";
Fluffy Potato
  • 57
  • 1
  • 10

2 Answers2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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"
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • 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. – Fluffy Potato Oct 07 '18 at 11:12