Need simple code tip for copying/replacing within and across tables 1

[ribsa3]

Hello

I am no hero with SQL yet, and usually use other mechanisms for this, but since I have none available at the moment, I hope someone has a few moments to give me a few code tips.

I am trying to clean up an article database with a number of tables. In order to accomplish this, I need to read a certain value pertaining to a certain article from one table to another. So what I am asking is:

1) How do I read the value of field "Location" from Table1 and write it into the "Location" field in Table2 where field "ArticleNo" is the same. (Looping through all records) Example:

Table1
ArticleNo Location
100 A 21

Table2
ArticleNo Location
100 B 16


2)In Table1, there might be double occurrences of a certain item as given by "ArticleNo" where the field "Warehouse" is different. I would like to take the value of the field "Quantity" in the records where "Warehouse" > 0 and add it to and update the "Quantity" field in the record where "Warehouse" = 0.
Example:

Table1
ArticleNo Warehouse Quantity
100 0 5
100 1 7

Resulting in:
ArticleNo Warehouse Quantity
100 0 12
100 1 0


I apologize for the simplicity and massiveness of this question, but it would save me a lot of trouble if anyone has the time to help me out with this.

Thanks in advance

Kindly,
Bjoern Sandvik



--------------------------------------------------------------------
Do what you wish, as long as it harms no one. That includes yourself.

 

[SQLSister]

i'll get you started with an example update statment.

UPDATE Table1
SET Field1 = Table2.Field2
FROM Table1 join Table2
ON Table1.IDField = Table2.IDField
WHERE Table2.Field3 = 'sometext'

BTW, you need to stop thinking of the concept of looping through records. This cannot be done efficiently. REcords should be affected as a set (as per the statement above) not one record at a time.

The second one is a bit harder. But le'ts get the first one done before tackling it.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ribsa3]

Thank you very much! That bit was a veritable success. Just glad it was so effortless, and yes - I see what you mean by recordsets now

-B




--------------------------------------------------------------------
Do what you wish, as long as it harms no one. That includes yourself.