32

I am using this script, trying to join 2 tables with 3 conditions and update T1: 

Update T1 set T1.Inci = T2.Inci 
ON T1.Brands = T2.Brands 
AND T1.Category= T2.Category
AND T1.Date = T2.Date

but I encounter:

Incorrect syntax near the keyword 'ON'.

Can't figure it out why.

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
marilyn
  • 545
  • 3
  • 12
  • 19

4 Answers4

54
UPDATE
    T1
SET
    T1.Inci = T2.Inci 
FROM
    T1
INNER JOIN
    T2
ON
    T1.Brands = T2.Brands
AND
    T1.Category= T2.Category
AND
    T1.Date = T2.Date
Robin Day
  • 100,552
  • 23
  • 116
  • 167
  • 4
    Does the `ON` and `AND` operators only verify equality? Could they do the equivalent of `if ((T1.Brands < T2.Brands) && (T1.Category > T2.Category))`? – Fernando Silva Feb 08 '14 at 00:26
  • If you use something like `>` or `<` you reduce the types on joins you can use. The equality operator works really well with hash joins, but the other operators don't. You could inadvertently land in a situation where your join costs `O(n^2)` time (nested loop join). – Ariel A Dec 20 '20 at 14:34
5

You need to do

Update table_xpto
set column_xpto = x.xpto_New
    ,column2 = x.column2New
from table_xpto xpto
   inner join table_xptoNew xptoNew ON xpto.bla = xptoNew.Bla
where <clause where>

If you need a better answer, you can give us more information :)

Bruno Costa
  • 2,708
  • 2
  • 17
  • 25
3
UPDATE T1,T2 
INNER JOIN T1 ON  T1.Brands = T2.Brands
SET 
T1.Inci = T2.Inci
WHERE
    T1.Category= T2.Category
AND
    T1.Date = T2.Date
philipxy
  • 14,867
  • 6
  • 39
  • 83
logsv
  • 544
  • 6
  • 17
0

You should join T1 and T2 tables using sql joins in order to analyze from two tables. Link for learn joins : https://www.w3schools.com/sql/sql_join.asp

Yuresh Karunanayake
  • 519
  • 1
  • 4
  • 10