-
SQL 2005 CROSS JOIN Query help
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'
-
update BADGE set TYPE= 16
FROM BADGE
JOIN UDFEMP
ON ... <-- your join statement here-->
where BADGE.TYPE = 66 udfemp.personneltype = 'NONE'
-
Join Statement
How would I determine what my JOIN Statement would be?
ON ... <-- your join statement here-->
-
[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
-
[QUOTE=bmallett1;129792]
Originally Posted by bmallett1
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?
-
[QUOTE=bmallett1;129793]
Originally Posted by bmallett1
How would we add the JOIN to the query?
The column that matches between BADGE & EMP is BADGE.EMPID with EMP.ID
-
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'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|