Subject | Re: [firebird-support] Can I insert data where some of the data is decided by the insert statement? |
---|---|
Author | Helen Borrie |
Post date | 2004-07-01T04:29:47Z |
At 02:08 AM 1/07/2004 +0000, you wrote:
INSERT INTO USERS (USER_NAME,USER_PASSWORD,IS_TEMPPLATE_RECORD)
SELECT
USERNAME,
USERSPASSWORD,
CASE WHEN (USERSPASSWORD = '.STYLE.') THEN 'T' ELSE 'F' END
FROM OLD_USERS;
Otherwise, no. SP necessary.
/heLen
>Hi, I have 2 tables: OLD_USERS, USERS.In Firebird 1.5, yes:
>The table(s) ordinarily store user records, but can also store
>templates for users. Someone in the past had the bad idea to indicate
>a template record by putting the string ".STYLE." into the password
>field!!
>
>So I'm trying to get the data from the old table to the new table
>(which has a column IS_TEMPPLATE_RECORD [T/F] ), and whereever I see
>".STYLE." set IS_TEMPPLATE_RECORD to true.
>What I want (which is obviously wrong between the second pair of
>brackets) is:
>
>INSERT INTO USERS (USER_NAME,USER_PASSWORD,IS_TEMPPLATE_RECORD)
>SELECT USERNAME, USERSPASSWORD, ( OLD_USERS.USERSPASSWORD IS '.STYLE.'
>) FROM OLD_USERS;
>
>So how do I achieve this? One option I guess is for a stored procedure
>(but I don't want to do that because I'm targeting more than 1
>database type). Another option is to have two inserts, one where
>OLD_USERS.USERSPASSWORD == '.STYLE.' and the other where
>OLD_USERS.USERSPASSWORD != '.STYLE.'
>
>But is there a way to do this in one statement?
INSERT INTO USERS (USER_NAME,USER_PASSWORD,IS_TEMPPLATE_RECORD)
SELECT
USERNAME,
USERSPASSWORD,
CASE WHEN (USERSPASSWORD = '.STYLE.') THEN 'T' ELSE 'F' END
FROM OLD_USERS;
Otherwise, no. SP necessary.
/heLen