Inserting From one table to another along with a constant

[abscoot]

I am trying to run a SQL command to update one table from another. I need to add a record for each member from the Members Table with the Current Year(2018) in in the “Coupon Year”(Integer type) field of The Coupons Table. I have the Coupon Year default value set to 2018 (changed from last year), but when I run this command it only inserts the member ID with nothing in the “Coupon Year” field. I tried setting the “Coupon Year” field to be required, but it won’t let me unless I drop it and add it on, and then it wipes out all previous years data.

INSERT INTO “Coupons” (“Member ID”)
SELECT “Member_ID” FROM “Members” WHERE “STATUS” = ‘ACTIVE’ ;

I think what I need is (but does not work)
INSERT INTO “Coupons” (“Member ID”, “Coupon Year”)
VALUES (SELECT “Member_ID” FROM “Members” WHERE “STATUS” = ‘ACTIVE’, 2018) ;

I can’t remember how I did it last year, driving me nuts ( I converted it from an Access DB last year)

[Ratslinger]

Hello,

You don’t specify the database used. If HSQL use:

INSERT INTO "Coupons" ("Member ID", "Coupon Year") SELECT "Member_ID", '2018' FROM "Members" WHERE "STATUS" = 'ACTIVE'

[abscoot]

This is what I was looking for, Thank you. I ended up doing it in 2 statements and replacing the NULL with 2018, But I saved this in the updates file for next year.

[Ratslinger]

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.