How do I insert a Select Max from another table

Question

This is the code I have and I am not sure why it doesnt work

Insert INTO TEventGolfers (intGolferID, intEventID) 
    Values((Select MAX(intGolferID) From TGolfers),4)

I want to insert 4 as the EventID and the max golfer id from the table TGolfers as the foreign key golfer id in the EventGolfers Table

What's expected to happen if someone later today inserts into TGolfers an even higher intGolferID value? (Your data in TEventGolfers will be out of date.) — jarlh, Nov 21 '19 at 08:27

score 3 · Accepted Answer · answered Nov 21 '19 at 08:03

3

You can try this-

Insert INTO TEventGolfers (intGolferID, intEventID) 
    Select MAX(intGolferID),4 From TGolfers

answered Nov 21 '19 at 08:03

Fahmi

1

The syntax used in OP should work as-is. – Salman A Nov 21 '19 at 08:31
@SalmanA, yup, I know but this answer seems neater to me that OP's:) – Fahmi Nov 21 '19 at 09:04
More neat is not an answer! – Salman A Nov 21 '19 at 09:04
@SalmanA, Okk, I'll keep it in my mind – Fahmi Nov 21 '19 at 09:05

1 Answers1