Re: SQL example of UPDATE a column that has ENCRYPT_AES()

On 10-Jun-2014 09:23 -0500, Gqcy wrote:

I have a simple table, with an encrypted password column:

insert into HIDDNPWDS values
( 'xxxsite'
, 'xxxuserid'
, ENCRYPT_AES('password_value','encryptionpassword')
, 'chgd-date'
, 'comment')

I can only drop the row and insert again....

Only ¿know how to? DELETE the row and then INSERT that row again.? That is, there is not an error with an UPDATE preventing the request, but instead an error with the syntax in an attempt to code the UPDATE, and thus the following inquiry asking how to use UPDATE instead of combining DELETE and INSERT.?:

How do I structure the UPDATE SET to get the password column to
update???

For example [untested, but AFaIK should function as written]:

create table qtemp/HIDDNPWDS
( Site char(7), UsrId char(10)
, PwdHash char(50) for bit data
, ChgDate date, Comment char(50)
)
;
insert into qtemp/HIDDNPWDS values
('xxxsite'
,'xxxuserid'
,ENCRYPT_AES('password_value','encryptionpassword')
,'chgd-date'
,'comment'
)
;
-- assuming the site + user-id act as an effective primary key:
update qtemp/HIDDNPWDS set
PwdHash = ENCRYPT_AES('new_pwd_value','encryptionpassword')
, ChgDate = current date
where Site = 'xxxsite'
and UsrId = 'xxxuserid'
;