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 2008 Forums
 Transact-SQL (2008)
 avoid cursor

Author  Topic 

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-08 : 09:28:27
The select query returns several fields i.e. field1, field2, ...

I now have alot of if statements and for each if statement there is a separate update or insert or delete.

Example

if (fieldName1='test' and fieldname2='yes') then
update tblmain set fieldmain= fieldName1 where ...

if (FieldName1 is null or Fieldname2 is not null) then
insert into tbl2 set fieldmain=fieldName2 ...

How do I place the update statement in the query while checking each record?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 09:36:55
update tbl
set fieldmain =
case
when FieldName1 is null or Fieldname2 is not null then fieldName2
when fieldName1='test' and fieldname2='yes' then fieldName1
else fieldName
end
where ...

Note in your if statements you seem to be updating the column twice fieldname2='yes' means that Fieldname2 is not null so if the where clause is the same then the first check is redundant as it will be overwritten by the second statement. I've put the second statement first in the case statement due to this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-08 : 09:41:13
Hi, thanks for the reply but I do not see what you are doing.
Let's say you have the sql query i.e.
select field1, field2, field3... from tbl1
and now you would like to do an INSERT into tbl2 i.e.
insert into tbl2 values ('test', tbl1.field1)

How is this done please?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-08 : 11:07:37
insert into tbl2(col_list)
select 'test', tbl1.field1 from table1

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 11:10:29
Is this connected to the original question?
I think te main problem is with the requirements here.
You need to separate what you want to do from how you want to do it - seems like you are trying to ask how to recode a method rather than an objective. If you can't define what is needed you are never going to get a reasonable method.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-08 : 11:29:40
Thank you all.
Go to Top of Page
   

- Advertisement -