CODEWITHSUNDEEP
mysqlms-access
BarisTa
BarisTa

Reputation: 1

Join on column and return null values - MS access

I have two tables:

Table Cars:

RegNr      Sold
-------   ------
ABC123    2015-12-27
ABC222   
ABC333    
ABC444

table Unit:

VIN       BuyerPayed
------    ----------
ABC123    2015-12-18
ABC222    2015-12-18
ABC333    NULL
ABC444    2015-12-19

I see that ABC222 and ABC444 has recieved payment from the Buyer, so I want to have a SELECT statement that finds ABC222 ABC444.

I have tried 

Select cars.regnr, cars.sold, Unit.BuyerPayedDate from Unit Inner Join cars on cars.regnr= unit.vin where cars.sold is Null and Unit.BuyerPayed is Not Null;

but I get some awkward result.

Help please.

Upvotes: 0

Views: 33

Answers (1)

fthiella
fthiella

Reputation: 49049

You can use an UPDATE query with a join:

update
  cars inner join unit on cars.RegNr = unit.VIN
set
  cars.Sold = unit.BuyerPayed

and (depending on the logic you want to implement) you might want to add this condition:

where
  cars.Sold IS NULL

this will update cars ABC222 and ABC444 but not ABC123 because it already has a Sold date.

If, instead of updating the table, you just want to SELECT the rows that need an update you can use this query:

select cars.RegNr
from
  cars inner join unit on cars.RegNr = unit.VIN
where
  cars.Sold IS NULL
  and unit.BuyerPayed IS NOT NULL

Upvotes: 1

Related Questions