Help w/ an Update statement
-
I need to update the password field in a Users table with the concatenation of firstname (2 char) + lastname (4 char) + birth day from the Students table. I tried the the following but the subquery retuns mor than one row. The users.username and studem.suiq are the same but there in no PK-FK relationship.
update users set password = (
select lower(substring(firstname,1,2)) + lower(substring(lastname,1,4)) + cast(datepart(day, birthdate) as varchar (2))
from studemo) where mustchangepassword = 1
Thanks
-
update u set password = 'ConcatWork'
FROM Users U inner join dbo.StuDemo SD ON U.UserID = SD.UserID
-
Thanks! If I understand correctly, the syntax should look something like:
update users set password =
(lower(substring(studemo.firstname,1,2)) + lower(substring(studemo.lastname,1,4)) + cast(datepart(day, studemo.birthdate) as varchar (2))
FROM users
inner join studemo ON users.username = studemo.ident
I getting an error: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
What am I missing?
-
update users set password =
(lower(substring(studemo.firstname,1,2)) + lower(substring(studemo.lastname,1,4)) + cast(datepart(day, studemo.birthdate) as varchar (2)))
FROM users
inner join studemo ON users.username = studemo.ident
-
d'oh!!! I can't tell you how many times I mis-counted those. Many thanks.
-
Ya... that's about the only great thing about 2005.. you can actually see the pairing of those parenthesis so you don't miss any and you don't have to count them manually!!
-
I couldn't disagree more!!! There are a lot of great things in SQL 2005. You couldn't pay me to switch back.
Here's a great thing that you probably don't even know about: auto-saving of open queries.
I had several different query windows open with multiple procedures that I was writing open. These were new procedures so there was no previous version to start with. Well, I stopped working to help one of the developers with something, and my computer went into hibernation mode. Unfortunately, it wouldn't come out of it, and I ended up turning it off and back on losing several hours worth of work.
When I opened SQL Management Studio on restart, it popped up a message saying that it detected that there were open queries when the server shut down and asked if I wanted to recover them. I said yes, and it opened up all 8 of the query windows I had open previously witht he queries in them.
Now tell me that's not great!!!!
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Sorry I didn't express myself well. I was reffering to ssms which is much slower and doesn't even support all the things EM supported. But I can't say that 2005 doesn't have great new tools
. -
Well, now we certainly have ground to agree on there!! Although I think it was a good move to combine the functionality of EM and QA into one, I think doing so forced the removal and reworking of a lot of the functionality so that it's not nearly as user friendly. There are good additions to it though. The ability to change the connection of a query window is one of my favorites though I really wish they had included a T-SQL command to do that so that I could easily write a script to run itself on all servers.
My biggest complaint so far is that it's not very good at remembering my password, and I'm constantly re-entering it when I open a query or switch the connection context of an existing window. As much as I love the connection switching, I've found that if I need to run a script on all 15 servers, it's faster to use the Registered Servers pane by right-clicking on the server -> hitting the C key (or highlight Connect) -> hitting the Q button (or clicking on New Query). I don't have to enter login credentials this way.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply