CODEWITHSUNDEEP
sqlsql-server
bartekmp
bartekmp

Reputation: 413

Copy of certain fields from one table to another in MSSQL

I am trying to accomplish a copy of some row's fields from one table to another. I'm trying that with this query:

update table1
set goal = t2.Goal, notes = t2.Notes
from
    Table2 AS t2
    join Table3 AS t3
        ON t3.ID = t2.PID
    join table1 as t1
        on t1.title = Title 
        and Name like t1.name + '%'

I need to join these first two tables to get the name and titles with the third one which uses title and name as identifiers. This query works, but not for all rows in table1 - there is some amount of rows, which have no copied data. What am I doing wrong?

Upvotes: 0

Views: 102

Answers (2)

Gane
Gane

Reputation: 120

Try something like this...

update Table1 
  set Goal = t2.Goal,
      Notes = t2.Notes
from
   Table2 AS t2
   join Table3 AS t3
        ON t3.ID = t2.PID 
where  
   Table1.Title = t3.Title AND
   t2.Name like (Table1.Name + '%')

Here is an example based on your schema http://sqlfiddle.com/#!6/a08cc/1

Upvotes: 1

Shahzad Riaz
Shahzad Riaz

Reputation: 354

I think you need to do something like this:

update t1
set t1.goal = t2.Goal, t1.notes = t2.Notes
from
    Table2 AS t2
    JOIN Table3 AS t3 ON t3.ID = t2.PID
    JOIN table1 as t1 ON t1.title = t3.Title 
    AND t2.Name like t1.name + '%'

Upvotes: 4

Related Questions