Complex Update Statement - SQL Server Forums

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

Transact-SQL (2000)

Complex Update Statement

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-15 : 15:32:04

I have a rather complex statement brewing in my head that won't come out. Maybe you can help.

Two tables are involved, one table is product sales, the other is a listing of Technical Service Network people and their associated customer numbers and product lines...

PRODSALES
---------
CUSTNO
SALEDATE
PRODLINE
TSN
...

TSNCUST
---------
CUSTNO
TSN
PRODLINE
STARTDATE
ENDDATE
...

So, what I need to do is update each sales record with the appropriate TSN when the TSN is assigned to the customer and product line for the sale date. That's simple...

UPDATE PRODSALES
SET TSN = TSNCUST.TSN
WHERE CUSTNO =
(SELECT CUSTNO FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = PRODSALES.PRODLINE AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE)

The problem is with PRODLINE. If the above fails to find a TSN then I also want it to look for a TSN with the same conditions as above but a different product line as follows...

1. Look for PRODLINE = 10 if PRODSALES.PRODLINE <> 10
2. If above fails then look for PRODLINE = 20

It's in there somewhere, just won't come out! Thanks for your help...

Ken

Edited by - Ken Blum on 05/15/2003 15:45:00

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 16:13:56

Can you ever have 2 PRODLINES for the same key of:

CUSTNO, PRODLINE, STARTDATE, ENDDATE

What would happen then?

You would get:

quote:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Brett

8-)

Edited by - x002548 on 05/15/2003 16:20:32

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-16 : 15:38:10

No, there is only one TSN for one Customer for one Production Line for any particular time period and they do not overlap. This is checked in the Front-End App.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 15:47:08

Why don't you do some analysis and try:

SELECT * FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = '10' AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE

SELECT * FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = '20' AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE

ect

Brett

8-)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-19 : 12:27:52

I only want to look for a TSN for Product Line 10 if there is no TSN for the existing product line which would be something other than 10.

I only want to look for a TSN for Product Line 20 if the above statement fails above and there is no TSN for Product Line 10.

Please note that this is an update statement. Are you saying that I should get the TSN via a SPROC and assign it that way?

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-19 : 14:32:48

No, I'm suggesting that you to determine what your population looks like:

quote:

Why don't you do some analysis and try:

Also because if the 2 different criteria, I would say you have 2 different SQL statements.

Brett

8-)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-19 : 14:56:09

The population of this database will be changing daily, so I do not understand what I should be analyzing.

How would I invoke mutiple SQL Statments into a single UPDATE Statement? The only way I can think of is to write a SQL function that will return the TSN based on my criteria. Then I could do a...

UPDATE PRODSALES SET TSN = Get_TSN(Prodsales.Custno,ProdSales.SaleDate,ProdSales.ProdLine)

The Get_TSN() function would then return the appropriate TSN based on 3 possible SQL Statements...

SELECT CUSTNO FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = PRODSALES.PRODLINE AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE)

If the above returns no records then do same for TSNCUST.PRODLINE = 10

If the above returns no records then do the same for TSNCUST.PRODLINE = 20.

Return Result.

I was hoping I could accomplish this in a single UPDATE statement, but I guess not.

Ken

Edited by - Ken Blum on 05/19/2003 15:10:15

Edited by - Ken Blum on 05/19/2003 15:14:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources