Merge sytax help

Transact-SQL
1

Hi,

I got this next sample code working, however, the enhanced one failed (incorrect syntax near keyword where). All I try to do is to make the source part into a join with a where clause to limit the size). What is wrong?

  1. correct one
    merge into mytable as Target
    using mytable2 as Source
    on Target.id=Source.id
    when matched then
    update set Target.name=Source.name,
    Target.Salary = Source.Salary
    when not matched then
    insert (id,name,salary) values (Source.id,Source.name,Source.Salary);

  2. failed one
    merge into mytable as Target
    using mytable2 t2 join mytable3 t3 on t2.ID=t3.ID where t2.ID in ('123','456') as Source
    on Target.id=Source.id
    when matched then
    update set Target.name=Source.name,
    Target.Salary = Source.Salary
    when not matched then
    insert (id,name,salary) values (Source.id,Source.name,Source.Salary);

Thanks!

2

Try changing this

using mytable2 t2 join mytable3 t3 on t2.ID=t3.ID where t2.ID in ('123','456') as Source

to this

using ( SELECT * FROM mytable2 t2 join mytable3 t3 on t2.ID=t3.ID where t2.ID in ('123','456') ) as Source

Edit: Just noticed that you have column ID in both mytable2 and mytable3. So the subquery will have more than one column with the same name, which will confuse/cause error. So instead of SELECT *, specify explicity column names - such as SELECT t3.ID, t3.Name etc.

3

Thanks! That worked!