Doing an Update Ignore in SQL Server 2005

I have a table where I wish to update some of the rows. All the fields are not null. I'm doing a sub-query, and I wish to update the table with the non-Null results.

See Below for my final answer: In MySQL, I solve this problem by doing an UPDATE IGNORE. How do I make this work in SQL Server 2005? The sub-query uses a four-table Join to find the data to insert if it exists. The Update is being 开发者_如何学Pythonrun against a table that could have 90,000+ records, so I need a solution that uses SQL, rather than having the Java program that's querying the database retrieve the results and then update those fields where we've got non-Null values.

Update: My query:

UPDATE #SearchResults SET geneSymbol = (
    SELECT TOP 1 symbol.name FROM 
        GeneSymbol AS symbol JOIN GeneConnector AS geneJoin 
            ON symbol.id = geneJoin.geneSymbolID
        JOIN Result AS sSeq ON geneJoin.sSeqID = sSeq.id 
        JOIN IndelConnector AS joiner ON joiner.sSeqID = sSeq.id 
    WHERE joiner.indelID = #SearchResults.id ORDER BY symbol.id ASC)
WHERE isSNV = 0

If I add "AND symbol.name IS NOT NULL" to either WHERE I get a SQL error. If I run it as is I get "adding null to a non-null column" errors. :-(

Thank you all, I ended up finding this:

UPDATE #SearchResults SET geneSymbol = 
    ISNULL ((SELECT TOP 1 symbol.name FROM 
        GeneSymbol AS symbol JOIN GeneConnector AS geneJoin 
            ON symbol.id = geneJoin.geneSymbolID
        JOIN Result AS sSeq ON geneJoin.sSeqID = sSeq.id 
        JOIN IndelConnector AS joiner ON joiner.sSeqID = sSeq.id 
    WHERE joiner.indelID = #SearchResults.id ORDER BY symbol.id ASC), ' ')
WHERE isSNV = 0

While it would be better not to do anything in the null case (so I'm going to try to understand the other answers, and see if they're faster) setting the null cases to a blank answer also works, and that's what this does.

Note: Wrapping the ISNULL (...) with () leads to really obscure (and wrong) errors.

---

with UpdatedGenesDS (
select joiner.indelID, name, row_number() over (order by symbol.id asc) seq
from
GeneSymbol AS symbol JOIN GeneConnector AS geneJoin 
            ON symbol.id = geneJoin.geneSymbolID
        JOIN Result AS sSeq ON geneJoin.sSeqID = sSeq.id 
        JOIN IndelConnector AS joiner ON joiner.sSeqID = sSeq.id 
WHERE name is not null ORDER BY symbol.id ASC
)
update Genes
set geneSymbol = upd.name
from #SearchResults a 
inner join UpdateGenesDs upd on a.id = b.intelID
where upd.seq =1 and isSNV = 0

this handles the null completely as all are filtered out by the where predicate (can also be filtered by join predicate if You wish. Is it what You are looking for?

---

Here's another option, where only those rows in #SearchResults that are succesfully joined will be udpated. If there are no null values in the underlying data, then the inner joins will pull in no null values, and you won't have to worry about filtering them out.

UPDATE #SearchResults
 set geneSymbol = symbol.name
 from #SearchResults sr
  inner join IndelConnector AS joiner
   on joiner.indelID = sr.id
  inner join Result AS sSeq
   on sSeq.id = joiner.sSeqID
  inner join GeneConnector AS geneJoin
   on geneJoin.sSeqID = sSeq.id
  --  Get "lowest" (i.e. first if listed alphabetically) value of name for each id
  inner join (select id, min(name) name
               from GeneSymbol
               group by id) symbol
   on symbol.id = geneJoin.geneSymbolID     
 where isSNV = 0  --  Which table is this value from?

(There might be some syntax problems, without tables I can't debug it)