updating a column with data from another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating a column with data from another

Author

Topic

scottdg
Starting Member

20 Posts

Posted - 2012-08-17 : 13:16:31

I am pretty new to SQL and I was wondering if someone could tell me how or point me to the best resource to find out how to update a columns with data from another table. I want to update passwords for a large group of people that have never been on our website and I want to make the password their first initial, last name and zip code (i.e. jsmith08527). Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:19:22

are name,zip fields and password field in the same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:31:05

if it is then its a matter of


UPDATE table
SET passwordval= COALESCE(FirstInitial,'') + LastName + ZipCode

if not you would require a join


UPDATE t
SET t.passwordval= COALESCE(t1.FirstInitial,'') + t1.LastName + t1.ZipCode
FROM yourtable t
JOIN Nametable t1
ON t1.relatedcol = t.relatedcol

if columns are NOT NULL you dont require COALESCE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottdg
Starting Member

20 Posts

Posted - 2012-08-18 : 12:23:43

thanks for the response.

The password is in a different table from the other customer info so I understand I have to join them. There is not column named FirstInitial. The initial would need to be removed from FirstName column.

I am going to look up how to do that but if you could provide some assistance I am happy to listen.

Also, the password field is encrypted in the web table. Any suggestions on how I can extract that information so that it can be sent to the use as part of a mail merge email?

Thanks again.
Scott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 12:45:15

quote:
Originally posted by scottdg

thanks for the response.

The password is in a different table from the other customer info so I understand I have to join them. There is not column named FirstInitial. The initial would need to be removed from FirstName column.

I am going to look up how to do that but if you could provide some assistance I am happy to listen.

Also, the password field is encrypted in the web table. Any suggestions on how I can extract that information so that it can be sent to the use as part of a mail merge email?

Thanks again.
Scott

How is the format of FirstName? based on format you might need to make use of string parsing function like CHARINDEX,PATINDEX to find position of separator and then get Initial part from it using SUBSTRING or LEFT
Getting encrypted value back is not possible unless you know how encryption was done. Didnt understand why you need to get encrypted password back as its a security violation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottdg
Starting Member

20 Posts

Posted - 2012-08-20 : 16:19:38

I don't need to get the current encryption back. What I meant is that whatever value I am writing to that column I am going to need. So if I am updating John Smith's password to jsmith08901 I need to be able to write that information out so that I can send it to the member in an e-mail.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 16:54:42

quote:
Originally posted by scottdg

I don't need to get the current encryption back. What I meant is that whatever value I am writing to that column I am going to need. So if I am updating John Smith's password to jsmith08901 I need to be able to write that information out so that I can send it to the member in an e-mail.

thats why i told
read back passwords is not recommended. why not you reset it instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottdg
Starting Member

20 Posts

Posted - 2012-08-21 : 08:16:29

I am resetting it. But I need to send the user the new reset password. If I am resetting it in an encrypted file is there a way I can get that information to send them? I am not sure how else I can explain it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 10:05:51

quote:
Originally posted by scottdg

I am resetting it. But I need to send the user the new reset password. If I am resetting it in an encrypted file is there a way I can get that information to send them? I am not sure how else I can explain it.

for reseting why do you need earlier password. you can simply apply a random sequence algorithm to reset and generate a new temp password and alert them to change it upon login next time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottdg
Starting Member

20 Posts

Posted - 2012-08-21 : 11:19:09

quote:
Originally posted by visakh16

quote:
Originally posted by scottdg

I am resetting it. But I need to send the user the new reset password. If I am resetting it in an encrypted file is there a way I can get that information to send them? I am not sure how else I can explain it.

for reseting why do you need earlier password. you can simply apply a random sequence algorithm to reset and generate a new temp password and alert them to change it upon login next time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Okay...I don't want the old password. I want the new one. I need to take the name and new generated password so that we can send it to the person in an e-mail.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 11:20:42

ok..Hope that dispenses with the need for getting back the encrypted password!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources