x

Update query

Hi everyone trying to put together an update query based on this select query.

 select u.login_name, e.first_name, e.last_name, e.e_mail, u.last_login,u.status
 from table_user U
 inner join table_employee e on e.employee2user=u.objid
 where u.status=1
 and last_login <'2010-05-22 00:01:00.000'
 or last_login ='1753-01-01 00:00:00.000'
 order by last_login

Essentially I want to do is update u.status to 0 where the 2 last login critera is met.

Cheers for any advice.

more ▼

asked Jun 03, 2011 at 04:48 AM in Default

avatar image

mickyd
162 11 11 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Whilst @Mrs_Fatherjack is right, I just wanted to add that if the join is important here, then it should also be included in the update - there may be employees in the table_user table that are not in the table_employee table - it's a subtle difference

 Update
      U.[Status] = 0
 from table_user U
 inner join table_employee e on e.employee2user=u.objid
 where 
      u.status=1
 and last_login <'2010-05-22 00:01:00.000'
 or last_login ='1753-01-01 00:00:00.000'

That will update exactly the same subset of records as you got when you ran the result.

Also to reiterate @Pavel 's point too - should those last_login datetimes be within parentheses?

more ▼

answered Jun 03, 2011 at 06:12 AM

avatar image

Kev Riley ♦♦
65.9k 48 63 81

add U SET after update and it should be good to go. You wouldn't want parens around the 2 last_login datetimes as it changes the logic

Jun 03, 2011 at 04:16 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Try this:

 begin tran 
 go

 UPDATE table_user
 SET [STATUS] = 0 
 WHERE   [status] = 1
         AND last_login < '2010-05-22 00:01:00.000'
         OR last_login = '1753-01-01 00:00:00.000'

 Rollback tran 
 commit tran

Remember when you name columns that are the system as system defined e.g. status you need to put square brakes ([]) round them. See the status field above.

I have put a transaction round this as it's always sensible to use a transaction when running a script, if everything goes OK you then commit it, if not roll it back and nothing is lost.

more ▼

answered Jun 03, 2011 at 05:01 AM

avatar image

Mrs_Fatherjack
5.2k 65 68 77

many thanks for reply

get this back , when trying it.

Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "u.status" could not be bound.

Jun 03, 2011 at 05:30 AM mickyd

Sorry, the where statement needs to read:

WHERE [status] = 1 AND last_login

without the u. in front of the status, I've edited the answer.

Jun 03, 2011 at 05:39 AM Mrs_Fatherjack

Only a note, is your condition correct? Aren't you missing brackets there? AS the AND operator has higher priority than OR operator. So it will be processed AS ([status] = 1 AND last_login < '2010-05-22 00:01:00.000') OR last_login = '1753-01-01 00:00:00.000'. And if the [Status] = 1 should be valid for both last_login, then the last_login = '1753-01-01 00:00:00.000' is not necessary as '1753-01-01 00:00:00.000' is lower than '2010-05-22 00:01:00.000'

Jun 03, 2011 at 06:03 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x428
x172

asked: Jun 03, 2011 at 04:48 AM

Seen: 1056 times

Last Updated: Jun 03, 2011 at 01:31 PM

Copyright 2017 Redgate Software. Privacy Policy