update some fields from another table

[ponneri]

Hello Everyone !

I have 2 tables, namely 'now' with 150 records and 'req' with 380 records.

now :
====
ncode - text
nqty - integer
nstat - text

req :
====
rcode - text
rqty - integer
xqty - integer ( newly added column with no values as of now)
rstat - text

Now, how do I update the 'req' tables xqty & rstat fields with 'now' table's nqty & nstat values; where only the ncode and rcode of both tables are same.

If possible, I need to put a 0 (zero) in the 'req' table's xqty field when the ncode and rcode do not match.

I hope I am clear.

Looks simple but I'm not sure if I can get it right without messing up the records.

Any help will be appreciated.

 

[CJ_London]

Looks simple but I'm not sure if I can get it right without messing up the records.

take a backup before doing anything

now is a bad name for a table - it is a function which returns the current date and time. Strongly recommend you change it to something else

with regards your query it would be something like

UPDATE req
SET xqty=nz(nqty,0), rstat=nz(nstat,"")
FROM req LEFT JOIN now ON req.rcode=now.ncode

 

[ponneri]

Thanks CJ.

The actual table name is different from what I mentioned in the example. I'm careful not to use reserved words. Thanks anyway