WHERE IN when multiple fields required to be uniqu

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 2000 Forums

SQL Server Development (2000)

WHERE IN when multiple fields required to be uniqu

Author

Topic

jeffx
Starting Member

1 Post

Posted - 2004-05-21 : 21:44:30

I am not a database developer but have dabbled with databases off and on. Some co-workers asked me to help them with an update query they couldn't get. Trying to be helpful I gave it my best shot. Now I am regretting it because no one can seem to figure it out and I can't let it go.

This is on a DB2 database on an AS/400. There are two tables in question:
Table 1
------
custno
deliveryno
delcode
many more fields that aren't important

Table 2
-------
custno
deliveryno
state
many more fields that aren't important

The primary key in table 1 would be custno and deliveryno. In table 2 it is custno and deliveryno.

The goal is to update table 1.delcode based on criteria determined in table2. This was my idea:
update table 1 set delcode='x' where custno in (SELECT cust...) AND deliveryno in (SELECT deliveryno ...)

This works syntactically but produces the wrong result. The wrong result is do to it takes customerno and deliveryno to be unique. I don't know how to do that. I have tried:
update table 1 set delcode=;x; what custno + delivery no in (SELECT custno + deliveryno)...didn't work the + operator wasn't nice.

Tried a select into but couldn't do that either.

Can this be done? If so some pointers would be nice.

I hope I did a good job explaining this.

Regards,
J

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-21 : 23:44:23

You probably want something like this:

UPDATE t1
SET delcode = 'x'
FROM
Table1 t1
INNER JOIN Table2 t2 ON t1.custno = t2.custno
AND t1.deliveryno = t2.deliveryno

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-22 : 11:03:24

[code]
update t1 set delcode = 'x'
where exists (select from t2
where t2.custno = t1.custno
and t2.deliveryno = t1.deliveryno)
[/code]

quote:

This is on a DB2

DB2 does not support the non-standard from clause in an update statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources