Search code examples
ms-accesssql-updateis-empty

Update MS Access table empty fields based on same table

In MS Access database, I'm working with a table that has rows. In some cases not all columns of the rows are filled. I want to create an update query to update the values of the empty fields with data from other rows where the column is not empty.

googled the question but no satisfying answer has been found. Can someone show me how the query should be build?

Current table looks like

| Invoicenumber | Customer | Date    |
|---------------|----------|---------|
| 5             | 12       | 12-6-19 |
| 5             |          | 12-6-19 |
| 5             |          | 12-6-19 |
| 5             |          | 12-6-19 |
| 6             | 18       | 15-6-19 |
| 6             |          | 15-6-19 |
| 6             |          | 15-6-19 |
| 7             | 20       | 20-6-19 |
| 7             |          | 20-6-19 |

I need the table to look like this after updating:

| Invoicenumber | Customer | Date    |
|---------------|----------|---------|
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 6             | 18       | 15-6-19 |
| 6             | 18       | 15-6-19 |
| 6             | 18       | 15-6-19 |
| 7             | 20       | 20-6-19 |
| 7             | 20       | 20-6-19 |
Solution

  • You can do it with just SQL by joining the table to itself:

    UPDATE 
    Invoices 
    INNER JOIN Invoices AS Inv2
    ON Invoices.InvoiceNumber = Inv2.InvoiceNumber
SET
    Invoices.Customer = Inv2.Customer
WHERE 
    (Invoices.[Customer] Is Null)
    AND (Inv2.Customer IS NOT NULL)