CODEWITHSUNDEEP
sqljoin
user4552174
user4552174

Reputation:

SQL JOIN --- non-cartesian inner join

I have the following query:

--UPDATE
UPDATE p.fact_usersortcustomer
set
  cellid   = usc.cellid,
  dwupdatedate = getdate()
from s.userSortCustomer usc
JOIN t.append_fact_usersortcustomer  ON usc.actiondate = t.actiondate AND usc.ucdmid = t.ucdmid AND usc.sortid = t.sortid

But the update doesn't work because I am getting the following error:

[Error] Script lines: 10-17 ------------------------ ERROR: Target table must be involved in a non-cartesian inner join

Any ideas?

Upvotes: 0

Views: 127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

The table after your update is not mentioned in the from. This causes the "cartesian product". Presumably, you want something like this:

UPDATE p
    set cellid= usc.cellid,
        dwupdatedate = getdate()
     from s.userSortCustomer usc join
          t.append_fact_usersortcustomer t
          ON usc.actiondate = t.actiondate AND usc.ucdmid = t.ucdmid AND usc.sortid = t.sortid join
          p.fact_usersortcustomer p
          ON . . . ;

However, I do not know what the joinconditions are. They go where the . . . is.

Upvotes: 1

Jean-François Savard
Jean-François Savard

Reputation: 21004

I think you want to use

UPDATE p.fact_usersortcustomer usc
set
  cellid = usc.cellid,
  dwupdatedate = getdate()
FROM (select ucdmid, sortid, actiondate from append_fact_usersortcustomer) a    
WHERE usc.ucdmid = a.ucdmid
  AND usc.sortid = a.sortid
  AND usc.actiondate = a.actiondate;

Upvotes: 1

Related Questions