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.
| 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.Exampleif (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 tblset fieldmain =casewhen FieldName1 is null or Fieldname2 is not null then fieldName2when fieldName1='test' and fieldname2='yes' then fieldName1else fieldNameendwhere ...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. |
 |
|
|
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? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-08 : 11:07:37
|
| insert into tbl2(col_list)select 'test', tbl1.field1 from table1MadhivananFailing to plan is Planning to fail |
|
|
|
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. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-07-08 : 11:29:40
|
| Thank you all. |
|
|
|
|
|
|
avoid cursor