none
Select top N rows and only when exact N rows are fetch update those rows in a single query.(merge statement)

    Question

  • Is it possible to write the below query using merge statement.

    DECLARE @intCount INT
    SET @intCount = 1
    
    DECLARE @tblStudent AS TABLE
    (
    id int,
    name varchar(100),
    status char(1),
    Data VARCHAR(10) DEFAULT NULL
    )
    
    INSERT INTO @tblStudent(id,name,status) VALUES(1,'A','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(2,'B','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(3,'C','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(4,'D','N')
    ;with mycte as
    (
    select top (@intCount) * from @tblStudent WHERE status = 'N'
    )
    Update mycte
    SET Data = 'X'
     
    
    SELECT * FROM @tblStudent

    Monday, October 07, 2013 5:56 AM

Answers

  • Are you looking for this,

    DECLARE @intCount INT
    SET @intCount = 1
    
    DECLARE @tblStudent AS TABLE
    (
    id int,
    name varchar(100),
    status char(1),
    Data VARCHAR(10) DEFAULT NULL
    )
    
    INSERT INTO @tblStudent(id,name,status) VALUES(1,'A','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(2,'B','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(3,'C','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(4,'D','N')
    
    
    SELECT * FROM @tblStudent
    
    
    Merge @tblStudent t using (select top (@intCount) * from @tblStudent WHERE status = 'N')s
    on t.id = s.id
    WHEN MATCHED then
    update set 
    Data = 'X';
    
    
    
    SELECT * FROM @tblStudent
    


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    • Marked as answer by luckyforu2006 Monday, October 07, 2013 11:24 AM
    Monday, October 07, 2013 6:53 AM

All replies

  • Please elaborate your issue,

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Monday, October 07, 2013 6:28 AM
  • I am trying to write a sql statement which will hit database only once instead of hitting twice. I have read on web that merge keyword gives that benefits.

    steps for statement will be.

    1. get the top N data based on the where clause from the db(same table)

    2. if N top rows are returned then update those row(only when rows are returned).(same table)

    e.g. if I am getting top 2 rows from the database then update the table only when top 2 are returned.(within same table)

    Monday, October 07, 2013 6:35 AM
  • Are you looking for this,

    DECLARE @intCount INT
    SET @intCount = 1
    
    DECLARE @tblStudent AS TABLE
    (
    id int,
    name varchar(100),
    status char(1),
    Data VARCHAR(10) DEFAULT NULL
    )
    
    INSERT INTO @tblStudent(id,name,status) VALUES(1,'A','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(2,'B','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(3,'C','Y')
    INSERT INTO @tblStudent(id,name,status) VALUES(4,'D','N')
    
    
    SELECT * FROM @tblStudent
    
    
    Merge @tblStudent t using (select top (@intCount) * from @tblStudent WHERE status = 'N')s
    on t.id = s.id
    WHEN MATCHED then
    update set 
    Data = 'X';
    
    
    
    SELECT * FROM @tblStudent
    


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    • Marked as answer by luckyforu2006 Monday, October 07, 2013 11:24 AM
    Monday, October 07, 2013 6:53 AM
  • yes but I am getting below error.

    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '@tblStudent'.
    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near 's'.

    Monday, October 07, 2013 8:29 AM
  • I could actually see the results,

    /********
    
    id	name	status	Data
    1	A	Y	NULL
    2	B	Y	NULL
    3	C	Y	NULL
    4	D	N	X
    
    ******/
    

    Can you post the query that you are trying ?


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Monday, October 07, 2013 8:35 AM
  • exactly the same sql statement that you posted. I am using sql server 2012
    Monday, October 07, 2013 8:41 AM
  • May be, its because of your compatibility. MERGE will work only for compatibility level 100 and above(SQL Server 2008 and above).

    Try the belo and let us know your compatibility of your db.

    Select name,compatibility_level From sys.databases
    where name='your db'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 07, 2013 8:42 AM
  • I can see the output in sql server 2008 but not in 2012
    Monday, October 07, 2013 8:43 AM
  • I can see the output in sql server 2008 but not in 2012

    May be as I said in the previous responds, you will have less than 100 compatibility for your database.

    Try my above query and let us know to comment more. You may need to change the compatibility level to 100 or above  in this case.

    ALTER DATABASE database_name 
    SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 07, 2013 8:46 AM
  • Rewrite your query with Merge will not improve your query. It will make your update much slower.

    You can compare execution plans of the two queries. You will find your update with CTE is three times faster.

    Monday, October 07, 2013 4:10 PM