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
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 ='';
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 =''
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).
INSERT INTO ... SELECT ... WHERE is perfectly valid. The problem is that you cannot use a WHERE clause in a VALUES set-expression. That said, I agree that the user appears confused and seems to be trying to use INSERT to UPDATE a row.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.
