tsql query - select records from 2 tables without joining them

I have 2 table as follows:

emp_id emp_name  emp_add  emp_no dept_name
1      sss       hhh      0      hhh
2      wsss      ddd      0      hhh

2nd table is as follows:
dep_name dept_no
hhh      1

I have select rec开发者_开发知识库ords only from table 1 where dept_name matches with the second table. I cannot use joins because there are 300 records in table with matches with table 1 records. and also I want to set the value of emp_no in table 1 as dept_no of table 2.

Any help?

---

I see absolutely no reason to avoid using a join.

UPDATE t1
    SET emp_no = t2.dept_no
    FROM table1 t1
        INNER JOIN table2 t2
            ON t1.dept_name = t2.dept_name

---

Try this

select t1.*, (select top 1 dept_name from table2 t2 where t2.dept_no = t1.dept_no)
from table1 t1