How to update or select the same rows value in the table?

Question

Using Access 2003

Table1

EmpID  Name  city 

101    Raja  Sydney
102    Ram   Melbourne
103    Ravi  Adelaide   
101
102
103

So on…,

I want to update or select the same name, city for the blank Empid’s

Expected Output

EmpID  Name  city 

101    Raja  Sydney
102    Ram   Melbourne
103    Ravi  Adelaide   
101    Raja  Sydney
102    Ram   Melbourne
103    Ravi  Adelaide

So on...,

How do I make a query for this condition?

Answer 1

It is possible to use sub queries to update in Access:

UPDATE (
    SELECT tblT.EmpID, tblT.EmpName, tblT.City
    FROM tblT
    WHERE tblT.EmpName Is Null) AS t 
INNER JOIN (
    SELECT tblT.EmpID, tblT.EmpName, tblT.City
    FROM tblT 
    WHERE tblT.EmpName Is Not Null) AS x 
ON t.EmpID = x.EmpID 
SET t.EmpName = [x].[EmpName], t.City = [x].[City];

Be aware that the query design window can seriously mess up the layout and can stop sub queries from working by changing the parentheses () to square brackets [], however, it is safe enough to paste the SQL into SQL View, save and run.

Answer 2

You table as posted has no key and violates first normal form.

For example, if you table contained this data:

Table1

EmpID  Name  city 

101    Raja  Sydney
102    Ram   Melbourne
103    Ravi  Adelaide   
101    Ajaa  New York
102    Mar   Des Moines
103    Ivar  Dallas   
101
102
103

...then which value would you expect this query to pick for 102: Melbourne or Des Moines?