Update Statement - SQL Server Forums

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

Update Statement

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-01 : 10:39:51

Dear All,

I want to update a field for all rows in a table with default value ’LIST1’,’LIST2’ and ‘LIST3’ in following way

For eg:

FOI1334816 01/03/2012 Mr sharon Peckham LIST1
TOI3454563 07/12/2012 Mr elizabeth goddard LIST2
RTY8383831 12/06/2012 Mr Natalie LIST3

DoorFOI1334816 01/03/2012 Mr sharon Peckham LIST1
TOI3454563 07/12/2012 Mr elizabeth goddard LIST2
RTY8383831 12/06/2012 Mr Natalie LIST3
...
...

How to write update statement for this? Any help will be highly appreciated

Thanks
Regards,
SG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 10:43:22

[code]
UPDATE t
SET 3rdfield= CASE WHEN 1stfield LIKE 'FOI%' THEN 'LIST1'
WHEN 1stfield LIKE 'TOI%' THEN 'LIST2'
WHEN 1stfield LIKE 'RTY%' THEN 'LIST3'
END
FROM table
[/code]

make sure you replace fields with correct names

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 10:45:28

ummm..huh?

Post the DDL, and form the sample Data into DML Satements and show the expected results

use [ code] [ /code] tags (with out the space) to retain formatting

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-01 : 11:01:16

Dear visakh

Thanks for your reply.

Your query is based on field value ie LIKE 'FOI%' or LIKE 'TOI%'

But the value need not to be like this.Just for ex i mentioned this.

i just want to update

First row field with 'list1'

second row field with ' list2'

third row field with 'list 3'

4 th with 'list1'

5th row with 'list2'

and so one..

Hope you understand.Sorry if my explanation is not clear.

Thanks

SG

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 11:14:37

The order of data in a database is meaningless

how would you sort your data, and are you just trying to break up all the data into 4 separate lists? If so, for what purpose?

And do you know what DDL is?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 11:26:10

quote:
Originally posted by satheesh

Dear visakh

Thanks for your reply.

Your query is based on field value ie LIKE 'FOI%' or LIKE 'TOI%'

But the value need not to be like this.Just for ex i mentioned this.

i just want to update

First row field with 'list1'

second row field with ' list2'

third row field with 'list 3'

4 th with 'list1'

5th row with 'list2'

and so one..

Hope you understand.Sorry if my explanation is not clear.

Thanks

SG

when you say 1st 2nd etc on what basics you're ordering it?
there's no concept of ordering in a table unless you specify it by means of ORDER BY

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

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-01 : 11:33:19

Dear X002548

I am producing this record for email campaign.I need to order the data by expirydate either by desc/asc

Then i need to produce the 'list1' by reterving the data in the row 1,4,7,10 etc

second list - row 2,5,8,11 etc

thrid list - row 3,6,9,12 etc

I created a post 'Dividing the select query result' both for same purpose only

Sorry my explanation is not clear.

Thanks

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-01 : 11:36:32

Dear Visakh,

>>when you say 1st 2nd etc on what basics you're ordering it?
there's no concept of ordering in a table unless you specify it by means of ORDER BY<<

Yes Visakh.i am trying to Update the row by ordering by either ASC or desc

Thanks

Regards,
SG

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 12:11:40

But by what "Logic" do the offsets have ANY meaning.

Are you JUST trying to create 4 separate List of equal size?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 13:45:57

[code]
UPDATE t
SET 3rdfield= CASE WHEN (Rn-1)%3=0 THEN 'LIST1'
WHEN (Rn-1)%3=1 THEN 'LIST2'
WHEN (Rn-1)%3=2 THEN 'LIST3'
END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY expirydate ASC) AS Rn,* FROM table)t
[/code]

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 13:59:34

"Email Campaign" = SPAM

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources