CODEWITHSUNDEEP
c#sql
user6453809
user6453809

Reputation: 55

Using merge how can i use where statement?

i'm using this code:

SqlCommand cmd = new SqlCommand(@"
MERGE customermaster  AS target USING
      Tamio.dbo.memberform  AS source
      ON target.id  = source.id 
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
        INSERT (id, name) VALUES (source.id, source.name)
", conn);
cmd.ExecuteNonQuery();

Example:How can i execute mycode where active=1.

Upvotes: 0

Views: 52

Answers (2)

Steve
Steve

Reputation: 216293

Not able to test it now, but if I remember well this could be another way to reach your goal

WITH Source AS
(
    SELECT id, name 
    FROM Tamio.dbo.memberform  
    WHERE active = 1
)
MERGE INTO customermaster AS target
USING Source ON target.id = source.id 
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

However all this depends on which table you want to filter with Active=1. From your question is not clear if Active is a customermaster's field or memberform's field. In my answer I have assumed that is a memberform's field.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You should be able to use a subquery:

MERGE (SELECT cm.*
       FROM customermaster cm
       WHERE cm.active = 1
      ) AS target USING
      Tamio.dbo.memberform  AS source
      ON target.id  = source.id 
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
        INSERT (id, name) VALUES (source.id, source.name);

I am assuming that you are using SQL Server and active is in the master table.

Upvotes: 0

Related Questions