Update query not working

[EEEEE]

I have a simple query below that isnt giving me the results i need or not working at all. The query ran but it made null the wrong field


Table and field to be updated below
table = Master417DocLis t
field = DeaNumber


Table to update from below
table = IMSxportwCATSan dSLNlinked2Mast er
field = dea#

query below

UPDATE Master417DocLis t
SET Master417DocLis t.DeaNumber = (select IMSxportwCATSan dSLNlinked2Mast er.dea# from IMSxportwCATSan dSLNlinked2Mast er where Master417DocLis t.lastname = IMSxportwCATSan dSLNlinked2Mast er.[last name]
and Master417DocLis t.firstname = IMSxportwCATSan dSLNlinked2Mast er.[first name]
and Master417DocLis t.deaNumber is null)

it made Master417DocLis t.deaNumber = null instead of copying out from IMSxportwCATSan dSLNlinked2Mast er.dea#.

Thank you in advance!

EEEEE

[vijaii]

use the below query

UPDATE Master417DocLis t
SET Master417DocLis t.DeaNumber = IMSxportwCATSan dSLNlinked2Mast er.dea#
FROM Master417DocLis t
INNER JOIN IMSxportwCATSan dSLNlinked2Mast er ON
Master417DocLis t.lastname = IMSxportwCATSan dSLNlinked2Mast er.[last name]
and Master417DocLis t.firstname = IMSxportwCATSan dSLNlinked2Mast er.[first name]

I have a simple query below that isnt giving me the results i need or not working at all. The query ran but it made null the wrong field


Table and field to be updated below
table = Master417DocLis t
field = DeaNumber


Table to update from below
table = IMSxportwCATSan dSLNlinked2Mast er
field = dea#

query below

UPDATE Master417DocLis t
SET Master417DocLis t.DeaNumber = (select IMSxportwCATSan dSLNlinked2Mast er.dea# from IMSxportwCATSan dSLNlinked2Mast er where Master417DocLis t.lastname = IMSxportwCATSan dSLNlinked2Mast er.[last name]
and Master417DocLis t.firstname = IMSxportwCATSan dSLNlinked2Mast er.[first name]
and Master417DocLis t.deaNumber is null)

it made Master417DocLis t.deaNumber = null instead of copying out from IMSxportwCATSan dSLNlinked2Mast er.dea#.

Thank you in advance!

EEEEE

[debasisdas]

Both of you please follow posting guidelines and use proper CODE tag around the code are to make it more readable.

[siva538]

use the below query

UPDATE Master417DocLis t
SET Master417DocLis t.DeaNumber = IMSxportwCATSan dSLNlinked2Mast er.dea#
FROM Master417DocLis t
INNER JOIN IMSxportwCATSan dSLNlinked2Mast er ON
Master417DocLis t.lastname = IMSxportwCATSan dSLNlinked2Mast er.[last name]
and Master417DocLis t.firstname = IMSxportwCATSan dSLNlinked2Mast er.[first name]

Adding to this .. using a where condition to check the source and the destination columns whether equal or not helps in avoiding unnecessary updates and saves time and is a good practice to be followed.

The amount of time spend for checking the columns is less than updating the whole table every time.

If you have multiple columns to be updated where condition can be formed with
multiples <> equal checks with OR operator in between.

HTH

Sivakumar