Store Result of SELECT stament to be used to UPDATE another table

[yandoo]

Hi,

 

im trying to update a field of a table which is done through the results of a select statement.

 

I need to be able to a store the results of the select statement which can then be used to determine if an update another table is initiated.

 

This is where i have got so far:

 

 

 

SELECT laptops. * , loan.complete AS joinID

FROM laptops

LEFT JOIN loan ON loan.laptopname = laptops.laptopname

 

 

UPDATE laptops SET StatusID = 'AVA' WHERE complete = 'Y

 

 

The above displays records from 2 tables joining them. Laptopname from the laptop table with laptopname from loan table. The WHERE bit is displays the field of the loan table that indicates if the loan is complete or not.

 

From here i want to update the StatusID on the laptops table from the results of loan table WHERE it indicates thatthe complete field = Y.

 

E.G. the SELECT code generates the following

 

laptopname: STULAP01, STULAP02 // joined laptopname from laptops table and loan table

StatusID: AVA, OLN  // from laptops table

Complete: N, Y // from loan table

 

Next i need to UPDATE laptops SET StatusID ='AVA' WHERE  Complete = Y

 

 

Any help would be greatly appreciated

 

Thank You