Using SQL I need the correct syntax for updating one field with the contents of another field?

Question

Example:

Productcode /Desc. / Ischildofproductcode

MP001/ X /
Tx003 / / MP001

I need to take the value from the field “Desc.” In the MP001 record and give that value to the field “desc.” In the TX003 record using the “ischildofproductcode” field to make the comparison. **Please note, I need to do this on a large scale for about 40k records, I will be using MS Access.

Ok, lets say I am using MS ACCESS

lets say I import the excel file containing the data in question and I name the table (products)

Now I open up the query tab and I go to design view and i write the SQL statement.

I have 3 fields, productcode (which is the primary field) description, and ischildofproductcode

Now, the records which has the descriptions in them can be matched to the records that do not have the descriptions in them by the field "ischildofproductcode", the reocrd which does not have the description will have a value in this field which will match up with a "productcode" in another record which will have the description, how do I write that statement exactly?

I think we are getting close but not quite, the syntax you have given is for one record, but the questions states that I have many records and I was only using the example of 1.

Answer 1

Hmm... I am not sure about the naming you're using, but I will try:

UPDATE TX003 set TX003.Desc = (select MP001.Desc from MP001 where TX003 .Ischildofproductcode= MP001.Ischildofproductcode)

Answer 2

Update

Answer 3

I believe this will work although I am not sure about the access's support for sub-queries. ** Do a test run first! and backup your DB **

UPDATE products
SET Desc = (
SELECT products2.Desc
FROM products products2
WHERE products2.Productcode = products.Ischildofproductcode
)

Answer 4

2 points...

MS-Access? I thought you said large scale. Try updating 900 million records.

Most of the people responding are pretty close, you'll get it. Oh by the way, back up your "large" database until you get it right.

Answer 5

i'm not particular the coolest judgment works that way. you need to elect the cost you favor from table b then run a second question doing the replace with the cost that you purchased from the first question.

Answer 6

update (tablename)
set desc = (select distinct desc from (tablename) b
where b.productcode=a.ischildofproduct)
where desc=NULL

something like that maybe?