SQL Query and Update 2

[MichaelF81]

Ok, I want to run a query that pulls the 'userpwrd' from the 'users' and the 'users_bak' table where em_id is the same in both. So I see what the PW is in both tables, then update the 'users' table with the value of the 'userpwrd' in the 'users_bak'.

I have tried a few queries for the select statement

select userpwrd
from usersowpp_bak
where (select userpwrd from usersowpp_bak inner join usersowpp on usersowpp.userpwrd = usersowpp_bak.userpwrd)

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[monksnake]

I believe this will do all you need (not tested) please have a backup

UPDATE usersowpp set userpwrd = (select a.userpwrd from usersowpp_bak a where usersowpp.em_id = a.em_id)

<.

 

[gmmastros]

monksnake,

Wouldn't that set the password to null if the user does not exist in the backup table?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

hmmmm yes it would



<.

 

[monksnake]

ok How about this

UPDATE usersowpp set userpwrd = isnull((select a.userpwrd from usersowpp_bak a where usersowpp.em_id = a.em_id), userpwrd)

<.

 

[gmmastros]

That's better. I would have written it like this...

Update usersowpp
set    usersowpp.userpwrd = usersowpp_bak.userpwrd
From   usersowpp
       Inner Join usersowpp_bak
         On usersowpp.em_id = usersowpp_bak.em_id

Because of the inner join, if there is not a match in the backup table, then it will not update the 'real' table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

I see, that is by far more efficient.
In SQL I will definitly take your word over mine

<.

 

[MichaelF81]

Thanks guys!




"Adults are just obsolete children and the hell with them." - Dr. Seuss