asked on

Update query, cross join two tables

The statement reads:
"... , if you write an UPDATE statement with a table A in the UPDATE clause and a table B (but not A) in the FROM clause, you get an implied cross join between A and B."

Question: Could you possibly make #A and #B to demo the effect of this statement?

Based on above, it seems the query will be:

Update #A
Set Age_InA = Age_InB
From #B

Feel free to make your own SQL to better illustrates it. BTW, the book I am studying is using the following example:

UPDATE Sales.MyCustomers
SET MyCustomers.country = SRC.country,
MyCustomers.postalcode = SRC.postalcode
FROM Sales.Customers AS SRC
-- WHERE MyCustomers.custid = SRC.custid;   -- with WHERE clause this will be like inner join.

Open in new window

Scott Pletcher

No need for any of this mess and ambiguity in SQL Server. Just always explicitly join the two in the UPDATE statement:

UPDATE a
SET Age_InA = b.Age_InB
FROM #A a
INNER JOIN #B b ON ... = ...

Mike Eghtebas

ASKER

Thank you for the comment. I wanted just to see how this acts as cross join running something like:

UPDATE Sales.MyCustomers
SET MyCustomers.country = SRC.country,
MyCustomers.postalcode = SRC.postalcode
FROM Sales.Customers AS SRC

Open in new window

I wanted just to understand this poor method and what kind of damage it causes. This is sort of investigation of a bad process.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

Thank you.