• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

SQL Syntax

select * from #table1 where len(warehouse_id) = 0

UPDATE      #table1
SET warehouse_id =
         WHEN LEN(RTRIM(x.warehouse_id)) > 0 then x.warehouse_id
         WHEN LEN(RTRIM(x.whse_id)) > 0 then x.whse_id
         WHEN LEN(RTRIM(n.warehouse_id)) > 0 then n.warehouse_id
         ELSE '' END
FROM      #table1 WITH (NOLOCK)
INNER JOIN (SELECT distinct warehouse_id, whse_id, order_id
                    FROM order WITH (NOLOCK)
          WHERE (len(warehouse_id)> 0 OR len(whse_id) > 0)) x
          ON  #table1.order_id = x.order_id      
          INNER JOIN (SELECT distinct warehouse_id, order_id
                    FROM netwrk WITH (NOLOCK)
                    WHERE len(warehouse_id)> 0) n
          ON n.order_id = #table1.order_id                            
WHERE      len(RTRIM(#table1.warehouse_id)) = 0  

i have a temp table (#table1), that i am trying to update the warehouse id based on a lookup in two other tables.  for some reason i am not getting the expected results.  What am i doing wrong?
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How are you handling NULL values?

SELECT LEN(RTRIM(NULL)) returns a NULL, which is not evaluate to > 0.

For all of these comparisons you'll need to do something like...

LEN(RTRIM(COALESCE(whatever, '')))
Are you sure about doing two inner joins on different tables? You know that's gonna give you "only" the rows that match the id in "both" tables.

If you want to update different sets of rows, you can do left outer joins and handle the nulls like jimhorn says, or you can do two updates.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now