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

update script

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-09-28 : 19:56:54

Hello guys,

I have a table like below format, i need to update Date3 field with following criteria.
1)If Field1=Field2, update Date3 with Date1 value
2)If Field1<>Field2, match the Field2's value with Field1's data than take Date1 value and update with Date3
3)IF Field1<>Field2 and Date1 and Date2 is null, update Date3 with null value

for more clear take a look output.

declare @tbl1 table
(Field1 varchar(50),
Field2 varchar(50),
Date1 varchar(50),
Date2 varchar(50),
Date3 varchar(50)
)
INSERT INTO @tbl1
SELECT 'A001', 'A001','10/23/2008',null,null

INSERT INTO @tbl1
SELECT 'A002', 'A001',null,'11/25/2008',null

INSERT INTO @tbl1
SELECT 'A003', 'A002',null,'11/14/2008',null

INSERT INTO @tbl1
SELECT 'A004', 'A004',null,null,null

INSERT INTO @tbl1
SELECT 'A005', 'A004',null,'08/18/2010',null

Output:
Field1 Field2 Date1 Date2 Date3
A001 A001 10/23/2008 null 10/23/2008
A002 A001 null 11/25/2008 10/23/2008
A003 A002 null 11/14/2008 10/23/2008
A004 A004 null null null
A005 A004 null 08/18/2010 08/18/2010

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 01:46:12

You can write case conditions in your update statements.Something like this


update YourTable 
set Date3 =case when  Field1=Field2 then Date1 
           else 
           case when  Field1<>Field2 and Date1 isnull and Date2  isnull then null end 
           end,
    Date1 =case ... then ... else ... end

PBUH

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-09-29 : 07:59:23

getting erro. can someone fixe the error

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 08:04:20

Post the error.

PBUH

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-09-29 : 09:17:43

I tried below but does not update rows 2,3,4 and 5 values.

in row 2, the value will be 1 row's date1, in row 3, same as row 2, in row 4, null and row 5, row 5's date2 value.

UPDATE @tbl1 SET Date3 = CASE
WHEN Field1=Field2 and Date1 is not null THEN Date1
WHEN Field1<>Field2 and Date1 is null THEN Date1
ELSE Date2
END
select * from @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-09-29 : 11:08:21

guys please??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-09-29 : 16:16:38

This seems to work given your sample data. I doubt it will work with your actual data as it makes some assumptions that may not hold true.


;with cte 
as 
(
       select Field1, Field2, Date1, Date2, coalesce(Date1, Date2) date3
       from   @tbl1
       where  Field1 = Field2
       Union all
       select b.field1, b.field2, b.date1, b.date2, coalesce(a.date3, b.date2)
       from   cte a
       join   @tbl1 b on b.Field2 = a.field1
       where  b.field1 != b.field2
)
update a set
       a.Date3 = b.Date3
from   @tbl1 a
join   cte b on b.Field1 = a.Field1

select * from @tbl1 order by 1

OUTPUT:
Field1          Field2          Date1           Date2           date3
--------------- --------------- --------------- --------------- ---------------
A001            A001            10/23/2008      NULL            10/23/2008
A002            A001            NULL            11/25/2008      10/23/2008
A003            A002            NULL            11/14/2008      10/23/2008
A004            A004            NULL            NULL            NULL
A005            A004            NULL            08/18/2010      08/18/2010

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources