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

Have you tried something like this:

UPDATE table1
SET
    name = (SELECT t2.name
            FROM   table1 T2
            WHERE  t2.name is not null
                and t2.empid - table1.empid),
    city = (SELECT t3.city
            FROM   table1 T3
            WHERE  t3.city is not null
                and t3.empid - table1.empid)
WHERE
    name is null        
    and city is null

--Update--
Doh!!!

onedaywhen is right. You can't use a select in an update (in MS Access).

The ultimate solution appears to be to take this down into VBA, open up a recordset of the null names/cities, and a recordset of the the non-null names/cities. Then walk the two record sets, updating the null records appropriately. It will look something like this:

' Note: Typed, but not tested (kind of like dictated buy not read)
' Note: No real error handling has been included in this code
' Note: This has not been optimised and is likely bigger than O(n)
Dim db As Database
Dim rst As Recordset
Dim rst2 As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("select blah blah blah", _
                                            dbOpenDynaset, _
                                            dbSeeChanges)
If (Not rst.BOF) Then
    rst.MoveFirst
End If

while (not rst.eof)
    Set rst2 = db.OpenRecordset("select blah blah where empid = " & _
                                    rst("empid"), _
                                dbOpenDynaset, _
                                dbSeeChanges)
    If (Not rst2.BOF) Then
        rst2.MoveFirst
    End If

    if (not rst2.eof) then ' we only need the first one we find
        rst.edit
        rst("fieldname1") = rst2("fieldname1") 
        rst("fieldname2") = rst2("fieldname2") 
        rst.update
    end if
    rst.movenext
wend
rst.close
set rst = nothing

(btw., naming a column name can cause you a big pain in the butt later on; I'd consider renaming it empName)

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?

Answer 3

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.