Question Locked

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

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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 C[b]omplete = Y[/b]

Any help would be greatly appreciated

Thank You

4 replies

by tony hopkinson

Update laptops set StatusID = 'AVA' where StatusID <> 'AVA' and laptopname in (SELECT laptopname from loans Where complete= 'Y')

Be quicker if you could say where statusid = 'UNAVA' or some such.

by twanstall

Thank you Tony,

thats fantastic works great! What does <> syntax do please?

:)

This discussion is closed to new replies.