0
\$\begingroup\$

I have two very similar SQL statements

INSERT INTO user_profile (user_id, setting_id, value)
    SELECT id, 18, true
    FROM users
        WHERE NOT EXISTS (
            SELECT user_id, setting_id FROM user_profile
            WHERE id=user_id AND setting_id=18);

INSERT INTO user_profile (user_id, setting_id, value)
    SELECT id, 16, true
    FROM users
        WHERE NOT EXISTS (
            SELECT user_id, setting_id FROM user_profile
            WHERE id=user_id AND setting_id=16);

I'm repeating everything except for the setting_id. Is there a way to combine these two statements?

\$\endgroup\$
0

1 Answer 1

Reset to default
4
\$\begingroup\$

You'll want to create a stored procedure that takes your setting ID

    CREATE OR REPLACE FUNCTION InsertUserProfile(id INTEGER) 
RETURNS void AS $$
	BEGIN
	  INSERT  INTO user_profile
					( user_id ,
					  setting_id ,
					  value
					)
					SELECT  id ,
							id ,
							true
					FROM    users
					WHERE   NOT EXISTS ( SELECT user_id ,
												setting_id
										 FROM   user_profile
										 WHERE  id = id
												AND setting_id = @ID );
	END;
	$$ LANGUAGE plpgsql;

You'll then want to call it like this:

SELECT usp_InsertUserProfile(16)
\$\endgroup\$
3
  • \$\begingroup\$ The author of the post indicated the database is PostgreSQL, rather than TSQL, as such the above syntax in your answer would not work. Perhaps you can edit it to fit PostgreSQL syntax. \$\endgroup\$ Commented Dec 30, 2015 at 14:40
  • \$\begingroup\$ I've amended the solution for postgres \$\endgroup\$ Commented Dec 30, 2015 at 14:46
  • \$\begingroup\$ The code does not look correct after the edit. You now have two id (one is a function parameter and the other a table column, before it was @id and id ). \$\endgroup\$ Commented Jan 4, 2016 at 20:24

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.