CODEWITHSUNDEEP
sqlpostgresql
ambe5960
ambe5960

Reputation: 2000

INSERT INTO postgres not working

I hate asking about little stupid bugs like this, but I can't figure it out for the life of me...

This query:

INSERT INTO user_authentication(init_password_setup) VALUES( substr((md5(random()::TEXT)), 0, 10) ) WHERE (init_password_setup = NULL OR init_password_setup ='')

Is throwing this error:

 16:27:11 Kernel error: ERROR:  syntax error at or near "WHERE"

I tried running it as an insert select as well. Sincere thanks for any help it is greatly appreciated

Upvotes: 0

Views: 2353

Answers (4)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

INSERT statement doesn't have WHERE clause. But you can use INSERT ... SELECT statement:

INSERT INTO user_authentication (user_id, init_password_setup) 
    SELECT id, substr((md5(random()::TEXT)), 0, 10)
    FROM users 
    WHERE <some condition here>;

or just update existed records:

UPDATE user_authentication
SET init_password_setup = substr((md5(random()::TEXT)), 0, 10)
WHERE init_password_setup IS NULL OR init_password_setup ='';

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

If you want to modify an already existing row, you need an UPDATE statement not an INSERT statement.

Update will modify the existing row(s), Insert will add a new row in the table.

UPDATE user_authentication
  SET init_password_setup = substr((md5(random()::TEXT)), 0, 10)
WHERE init_password_setup IS NULL 
   OR init_password_setup =''

Upvotes: 3

Zepplock
Zepplock

Reputation: 29145

You can't do INSERT with a WHERE clause. If you are trying to INSERT, then just use VALUES. If you want to use where then use UPDATE.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

An insert statement has no where clause. Either insert new data or update existing data with the update clause (which can have a where clause).

Upvotes: 1

Related Questions