problem with SQL 2005 Update query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem with SQL 2005 Update query

Author

Topic

bmallett1
Starting Member

9 Posts

Posted - 2011-10-26 : 16:11:47

I am trying to update only BADGE.TYPE records that have "EMP" in the udfemp.personneltype column.

Here is the query I built but it moves all records instead of filtering by the Personneltype column of the UDFEMP table:

update BADGE
set TYPE = 16
where TYPE = 66
and exists
(select *
from udfemp
where personneltype = 'EMP')

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 16:21:57

UPDATE B
SET TYPE = 16
FROM BADGE B
JOIN udemp U
ON B.KEY = U.KEY
WHERE U.personneltype = 'EMP'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bmallett1
Starting Member

9 Posts

Posted - 2011-10-26 : 16:26:53

Looks great but still need to update only records that currently have BADGE.TYPE = 66

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 16:27:48

UPDATE B
SET TYPE = 16
FROM BADGE B
JOIN udemp U
ON B.KEY = U.KEY
WHERE U.personneltype = 'EMP'
AND B.Type = 66

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 16:29:51

ooopppps

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 16:50:53

Then there must be a table (or 2) that joins them. Use the same kind of logic -> Badge B INNER JOIN <join table> on <join condition> INNER JOIN udemp U ON ....

--
Gail Shaw
SQL Server MVP

bmallett1
Starting Member

9 Posts

Posted - 2011-10-26 : 17:13:57

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 18:19:18

Why are you cross joining? That's a cartesian product, each row of one table joining to each and every row of the other. Cross join two tables that each have 5 rows and you get 25 rows in the result set.

What's the column that matches between EMP and Badge?

--
Gail Shaw
SQL Server MVP

bmallett1
Starting Member

9 Posts

Posted - 2011-10-26 : 18:51:08

quote:
Originally posted by GilaMonster

Why are you cross joining? That's a cartesian product, each row of one table joining to each and every row of the other. Cross join two tables that each have 5 rows and you get 25 rows in the result set.

What's the column that matches between EMP and Badge?

--
Gail Shaw
SQL Server MVP

I just thought we needed to Cross Join so that BADGE & UDFEMP could be used via the EMP table.

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-27 : 06:48:46

Cross join is a cartesian product. It's not actually a join, it's every row of one table matching to every row of the other. I highly doubt that's what you want.

You have the matching column, you have an example of inner joins. Think you can get the update correct?

--
Gail Shaw
SQL Server MVP

bmallett1
Starting Member

9 Posts

Posted - 2011-10-27 : 08:07:32

quote:
Originally posted by GilaMonster

Cross join is a cartesian product. It's not actually a join, it's every row of one table matching to every row of the other. I highly doubt that's what you want.

You have the matching column, you have an example of inner joins. Think you can get the update correct?

--
Gail Shaw
SQL Server MVP

This is the working solution:

UPDATE A
SET A.[TYPE] = 16
FROM BADGE A INNER JOIN EMP B ON (A.EMPID = B.ID)
INNER JOIN UDFEMP C ON (B.ID = C.ID)
WHERE C.[personneltype] = 'EMP' AND A.[TYPE] = 66;

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 08:54:54

how does it look?

Does it work?

Want to post the table DDL and sample Data in DML form and what the expected results would be....

my esp usb port is clogged right now and I can't see excatly what you have today...sorry

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-27 : 09:44:00

Are you sure about the join columns (A.EMPID = B.ID) and (B.ID = C.ID)? If so, you don't even need the Emp table at all as you could just join A.EMPID = (which contradicts what you said earlier about them not having matching columns)

p.s. Please use meaningful aliases if you alias the table. Badge being A and Emp being B makes absolutely no sense and just makes your query harder to read and understand

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 09:57:48

well..gail doesn't mean herself...BUT

SQL Formatting, appropriate Aliases (Even if you like to use the whole table name, as some prefer) but Badge would be B, EMP would e, UDFEMP would be u..help tremendously, so much so that I can see a formatted query and KNOW What's wrong with it...otherwise...it's harder

And just a side note..UDFEMP????

Is this suppose to be a User Defined Function (UDF)?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bmallett1
Starting Member

9 Posts

Posted - 2011-10-27 : 10:22:06

quote:
Originally posted by X002548

well..gail doesn't mean herself...BUT

SQL Formatting, appropriate Aliases (Even if you like to use the whole table name, as some prefer) but Badge would be B, EMP would e, UDFEMP would be u..help tremendously, so much so that I can see a formatted query and KNOW What's wrong with it...otherwise...it's harder

And just a side note..UDFEMP????

Is this suppose to be a User Defined Function (UDF)?????

UDFEMP is User Defined Fields

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

This issue has been resolved by my query above. Thanks to all who assisted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources