SQL 2005 CROSS JOIN Query help

[bmallett1]

Hello, I have a SQL 2005 Database with two tables that I need to CROSS JOIN in an update query.

The first table is BADGE and column to update is TYPE.

The problem I am having is the part where I need to reference another table UDFEMP column PERSONNELTYPE

so the basic idea is:

update BADGE set TYPE= 16 where TYPE = 66 if udfemp.personneltype = 'NONE'

[skhanal]

update BADGE set TYPE= 16
FROM BADGE
JOIN UDFEMP
ON ... <-- your join statement here-->
where BADGE.TYPE = 66 udfemp.personneltype = 'NONE'

[bmallett1]

How would I determine what my JOIN Statement would be?


ON ... <-- your join statement here-->

[bmallett1]

[QUOTE=bmallett1;129791]How would I determine what my JOIN Statement would be?

Yes there is a table that joins the two. It is the EMP table and column is ID. If I build a View with the three tables I get:

dbo.EMP INNER JOIN
dbo.UDFEMP ON dbo.EMP.ID = dbo.UDFEMP.ID CROSS JOIN
dbo.BADGE

[bmallett1]

[QUOTE=bmallett1;129792]

How would I determine what my JOIN Statement would be?

Yes there is a table that joins the two. It is the EMP table and column is ID. If I build a View with the three tables I get:

dbo.EMP INNER JOIN
dbo.UDFEMP ON dbo.EMP.ID = dbo.UDFEMP.ID CROSS JOIN
dbo.BADGE

How would we add the JOIN to the query?

[bmallett1]

[QUOTE=bmallett1;129793]

How would we add the JOIN to the query?

The column that matches between BADGE & EMP is BADGE.EMPID with EMP.ID

[skhanal]

CROSSJOIN is a different beast, it will give you cartesian product. Try this

update BADGE set TYPE= 16
FROM BADGE
JOIN UDFEMP
ON BADGE.EMPID=EMP.ID
where BADGE.TYPE = 66 udfemp.personneltype = 'NONE'