script for update - 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

SQL Server 2005 Forums

Transact-SQL (2005)

script for update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 15:06:54

Hello guys,
I need script to updat records:

I have a simple query problems.
I have 5 fields on a table. The first one is Id, 2nd is text value, it has multiple same value.
but 3rd and 4th fields has different values.
So, what i need, update 5th fields with field 3rd field's value, if exist, if not exist update from 4th field. If both 2nd,3rd and 4th fields are null, 5th fields also null.
We have to group by 2nd field.

The 5th field will be update with first record of 3rd field

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-26 : 15:55:04

Hello;

If I were you I would consider using a CASE statement in your query.

Here is a link with some good ideas to implement your query.
[url]http://msdn.microsoft.com/en-us/library/ms181765.aspx[/url]

r&r

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 16:37:55

Thanks, revdnrdy,
I have a table like belo, and i want to upldate PDate looks like that output
ID, Name, SDate, CDate, PDate
1 Aa 12/12/2008 NULL 12/12/2008
2 Aa NULL NULL 12/12/2008
3 Ab NULL 11/11/2008 11/11/2008
4 Ac NULL NULL NULL
5 Aa NULL NULL 12/12/2008

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-26 : 17:46:23

Try this:

BEGIN TRAN
UPDATE YourTable
SET PDate = CASE
WHEN SDate IS NOT NULL THEN SDate
WHEN SDate IS NULL AND CDate IS NOT NULL THEN CDate
WHEN Name IS NULL AND SDate IS NULL and CDate IS NULL THEN NULL
END

SELECT * FROM YourTable
--Don't forget to do COMMIT TRAN or ROLLBACK TRAN after this depending on whether the results are what is expected

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 19:17:37

Thanks Skorch,
How do I group by name?
Can you look there are 3 'Aa' data on the table. On PDate, all 3 ('Aa') will be update '12/12/2008'

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-01-27 : 06:02:16

[code]SET PDate = COALESCE(Sdate, CDate)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:22:25

quote:
Originally posted by rudba

Thanks, revdnrdy,
I have a table like belo, and i want to upldate PDate looks like that output
ID, Name, SDate, CDate, PDate
1 Aa 12/12/2008 NULL 12/12/2008
2 Aa NULL NULL 12/12/2008
3 Ab NULL 11/11/2008 11/11/2008
4 Ac NULL NULL NULL
5 Aa NULL NULL 12/12/2008


Update t
SET t.PDate=tmp.PDate
FROM table t
INNER JOIN (SELECT Name,COALESCE(MAX(SDate),MAX(CDate)) AS PDate
            FROM Table
            GROUP BY Name) tmp
ON tmp.Name=t.Name

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-27 : 10:57:41

Thanks, bjoerns, visakh16
I am gonna an implement on my project.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 11:48:15

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources