3

I sometimes need to run basic updates on a join. For example:

UPDATE t1 SET col1 = 'val1'
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.ID = t2.t1_id
WHERE t2.col3 = 'val3'

This works perfectly, but for some reason, in MS SQL Management Studio Express, it wants to convert this to

UPDATE t1 SET col1 = 'val1'
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.ID = t2.t1_id
CROSS JOIN t2
WHERE t2.col3 = 'val3'

It adds a crossjoin for some reason that I don't understand.

Now my question is: Why does Management Studio think this is what I meant? It must have a genuine use, otherwise it wouldn't suggest it. Yet I have no idea how and when (and why).

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Martijn
  • 11,964
  • 12
  • 50
  • 96
  • in the SQL pane, yes, right after I hit execute. I know it sounds weird. If you want I can get you two screenshots before and after I hit execute. – Martijn Dec 21 '10 at 11:30
  • oh, it also happens when instead of clicking execute, you click 'Verify SQL Syntax' in the SQL pane – Martijn Dec 21 '10 at 11:34
  • 1
    I don't think I understand what you are saying, because as far as I know, the standard query execution window (right click on the database > New Query) will NEVER perform any kind of rewrite on your query. Could you elaborate as to how exactly you elicit this behaviour? – Mike Chamberlain Dec 21 '10 at 11:35
  • 1
    No, that one doesn't. But the query pane that you can open when browsing a table does. – Martijn Dec 21 '10 at 11:39
  • 1
    Other changes it makes are for example expanding * to all column names, and reorganising conditions – Martijn Dec 21 '10 at 11:47

2 Answers2

2

It must be a bug, because your fist code is rigth.

Try

Update t1 set col1 = 'val1' from table1 t1, table2 t2 where t1.id = t2.t1_id and t2.col3='val3'

It throws the same result, but it could be less efficient. Does your SQL-MS add any code? Strange...

Daniel Hermosel
  • 423
  • 3
  • 9
  • Only in the SQL pane that opens op when you browse a table. This is a common error for me when I use the 'wrong tab' in Management studio. – Martijn Dec 21 '10 at 11:28
  • I have the same behavior. If I run this type of update query in the "Edit Top 200 Rows" pane it wants to add the cross join into the query. If I run the same update statement in a query window then it works just fine. – jaredbaszler Mar 02 '15 at 23:07
1

According to MSDN a cross join like that is equivalent to an inner join. Perhaps it uses cross joins because a cross join can be used to create cartesian products as well as simpler joins - whereas an inner join is more limited.

amelvin
  • 8,919
  • 4
  • 38
  • 59