Update query expression?

Question

Hi..like to ask what is the expression for updating Tbl_A from the record in Tbl_B ?

Example like Tble_B got Emp No and Driving License. And Tble_A only alot of column including the Emp No and Driving License. I just want to update Tbl_A with those that ONLY appear in Tbl_B according to their Emp No.

Anyone can help on this...cos monthly, i have to update close to a thousand records MANUALLY.

Thanks alot.

Answer 1

Update Tbl_A A set A.DrivingLicense = (select B.DrivingLicense from Tbl_B B Where A.Emp_No=B.Emp_No)

Notes : this statement : (select B.DrivingLicense from Tbl_B B Where A.Emp_No=B.Emp_No) must return just one row. if in Tbl_B there are multiple records with same Emp_No , then the query will fail (error : multipe rows in singleton select).

Answer 2

So you need to update TBL_A with records that ONLY appear in TBL_B? That's called a "NOT IN" query.

Go to this webpage: http://www.intermedia.net/support/sql/sqltut.asp

And do a "find" on "NOT IN". You'll see a query that should give you inspiration on how to do your query.

For more practice, I recommend: http://sqlzoo.net/

Answer 3

Please post more details, like all the fields in both tables, and what fields in what table you're specifically needing to update.