Ask Your Question
0

Inserting From one table to another along with a constant

asked 2018-01-09 18:42:30 +0100

abscoot gravatar image

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)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-10 03:02:13 +0100

Ratslinger gravatar image

updated 2018-01-10 03:07:00 +0100

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'
edit flag offensive delete link more

Comments

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.

abscoot gravatar imageabscoot ( 2018-01-11 23:46:52 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-01-12 00:16:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-09 18:42:30 +0100

Seen: 37 times

Last updated: Jan 10 '18