Is there a better way to make this query?

Question

I would like to know if there is a better way to write the following query:

IF EXISTS (SELECT * 
           FROM   master_list 
           WHERE  code = 'r_params') 
  BEGIN 
      UPDATE master_list 
      SET    code = 'w_params' 
      WHERE  code = 'r_params' 
             AND NOT name_1 = 'pH' 
             AND NOT name_2 = 'Flow' 
             AND NOT name_3 = 'Temperature' 

      SELECT * 
      FROM   r_master_list 
      WHERE  code = 'r_params' 

      SELECT * 
      FROM   r_master_list 
      WHERE  code = 'w_params' 
  END

Thanks

What is your actual problem with your code? Doesn't it run? Please specify a concrete problem as questions asking for "better" solutions are off-topic at Stack Overflow. — timo.rieber, Apr 10 '14 at 19:27
possible duplicate of [Should I use != or <> for not equal in TSQL?](http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql) — , Apr 10 '14 at 19:27
Must you absolutely return two resultsets? Why not a single one where code is either r_params or w_params? — Crono, Apr 10 '14 at 19:29
Also, you are of course aware that the condition in your `EXISTS` query isn't the same as in your `UPDATE` statement? — Crono, Apr 10 '14 at 19:42

Drew · Answer 1 · 2014-04-10T19:48:19.807

0

SELECT 1 may be faster

IF EXISTS (SELECT 1 
           FROM   master_list 
           WHERE  code = 'r_params') 
  BEGIN 
      UPDATE master_list 
      SET    code = 'w_params' 
      WHERE  code = 'r_params' 
             AND name_1 <> 'pH' 
             AND name_2 <> 'Flow' 
             AND name_3 <> 'Temperature' 

      SELECT * 
      FROM   r_master_list 
      WHERE  code = 'r_params' 

      SELECT * 
      FROM   r_master_list 
      WHERE  code = 'w_params' 
  END

edited Apr 10 '14 at 19:48

answered Apr 10 '14 at 19:26

Drew

2,583
5
36
54

are you sure? I would have thought that the `EXISTS` function would be clever enough not to return any fields... – Crono Apr 10 '14 at 19:27
@Crono I do believe there may actually be some debate over the issue: http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/ – Drew Apr 10 '14 at 19:47
Seems like there are lots of people claiming this to be right, yet there are as many saying the opposite. Either way, I *do* see something good with this approach: it would allow you to search through your code base for queries starting with `SELECT *` without needlessly reaching such queries nested into `EXISTS` function. It's a bit of a long shot, but still that's valuable. – Crono Apr 10 '14 at 20:01
+1 I am not sure if it is faster but I think it makes the intent of the statement more clear – paparazzo Apr 10 '14 at 20:42

score 0 · Answer 2 · answered Apr 10 '14 at 19:27

if exists (select * from MASTER_LIST where Code = 'r_params')
begin
    update MASTER_LIST 
      set Code = 'w_params' 
      where Code = 'r_params' 
      AND  Name_1 <> 'pH' 
      AND  Name_2 <> 'Flow' 
      AND  Name_3 <> 'Temperature'

 select * from R_MASTER_LIST  WHERE Code = 'r_params'

 select * from R_MASTER_LIST WHERE Code = 'w_params'
end

Crono · Answer 3 · 2014-04-10T20:10:05.910

Putting aside the form in which the client expects to receive the data, a better way would be to combine the two resultsets into one:

IF EXISTS (SELECT * 
    FROM   master_list 
    WHERE  code = 'r_params') 
BEGIN 
    UPDATE master_list 
    SET    code = 'w_params' 
    WHERE  code = 'r_params' 
        AND name_1 <> 'pH' 
        AND name_2 <> 'Flow' 
        AND name_3 <> 'Temperature' 

    SELECT * 
    FROM   r_master_list 
    WHERE  code IN ('r_params', 'w_params')
END

This way you are making ONE query on r_master_list rather than two. The client will receive the exact same data; only it will be his responsibility to split it into distinct resultsets, should he need to. Performance and efficiency-wise, that should be the preferred solution.

UPDATE:

As Drew suggests you may also want to make your `EXISTS' function subquery like this instead:

IF EXISTS (SELECT 1 
FROM   master_list 
    WHERE  code = 'r_params')

There is quite a debate over this being more efficient or not. Drew seems to believe it is; I for one can't tell. BUT! There's still an advantage with this approach that Drew didn't mention: if you ever want to find SELECT queries using * for fields throughout your codebase, you will find it convenient that all of your EXISTS functions subqueries uses the SELECT 1 pattern so that you won't reach out to those needlessly. It may not be much but still I thought it was worth mentionning.

paparazzo · Answer 4 · 2014-04-10T20:38:42.693

This is redundant
One of the condition on the update is WHERE code = 'r_params'

IF EXISTS (SELECT * 
           FROM   master_list 
           WHERE  code = 'r_params')

This is sufficient

  UPDATE master_list 
  SET    code = 'w_params' 
  WHERE  code = 'r_params' 
         AND NOT name_1 = 'pH' 
         AND NOT name_2 = 'Flow' 
         AND NOT name_3 = 'Temperature' 

  SELECT * 
  FROM   r_master_list 
  WHERE  code = 'r_params' 

  SELECT * 
  FROM   r_master_list 
  WHERE  code = 'w_params'

The only difference is that you would get the last select even is there were no 'r_params'

The last two can be combined

SELECT * 
  FROM r_master_list 
 WHERE code in  ('r_params','w_params') 
 order by code

Is there a better way to make this query?

4 Answers4