I want to fill out empty notification_settings for each user that already exists. IDs (PKs) are auto-generated by Hibernate in each table. Here is the user Table :
CREATE TABLE lottery_user (
id int8 not null,
email varchar(255) not null,
password varchar(255) not null,
notification_settings_id int8,
role varchar (50) default 'USER',
registry_date TIMESTAMP default now(),
primary key (id)
ADD CONSTRAINT FK_USER_TO_NOTIFICATION_SETTINGS
FOREIGN KEY (notification_settings_id) REFERENCES notification_settings
);
And here is the notification_settings table which I need to fill out for users that don't have it filled out for them.
CREATE TABLE notification_settings (
id int8 not NULL ,
test1_events bool DEFAULT TRUE ,
test2_events bool DEFAULT TRUE ,
test3_events bool DEFAULT TRUE ,
test4_events bool DEFAULT TRUE ,
PRIMARY KEY (id)
);
Basically, I need to use "INSERT INTO notification_settings (test1_events, test2_events, test3_events, test4_events) VALUES (True, True, True, True)" something similar to that. And of course, condition should be something like this "where these rows are empty for users". I can't seem to get Syntax right.
BIG NOTE: SQL code is for presentation purpose, so you can have an idea what kind of tables I have. I just need to get INSERT script right. Tables are working fine, just need to generate notification_settings values for users that already exist.
Another Note: Using Flyway, so it's not just about Hibernate. If that has to do with anything.
Are you just looking for:
INSERT INTO notification_settings (id)
SELECT id
FROM user
WHERE id NOT IN (SELECT id FROM notifiation_settings)
You might be looking to insert into an identity field:
SET IDENTITY_INSERT my_table ON
Since your foreign key constraint goes from notification_settings to user, the condition "where these rows are empty for user X" does not apply to your schema. On the other hand - "I want to fill out empty notification_settings for each user that already exists" can be done by using an insert...select construct:
set @rank=0
select @maxid = max(id) from notification_settings
insert into notification_settings (id)
select @maxid + @rank:=@rank+1 as rank
from user
where notification_settings_id is null
What is interesting is how you put those newly generated IDs back into the user table. Homework assignment for next time :)
INSERT INTO notification_settings (id)
SELECT u.id
FROM user u
WHERE
not exists (SELECT * FROM notifiation_settings ns where ns.id=i.id)