Table Update Based on Comparison Results 2

[CousinBrucie]

Hello, I'm taking in the string comparisons I need, but now need to just update a column in another table based on this result. Here's a snippet of what I'm trying to accomplish with a CASE WHEN THEN END but it isn't working:

SELECT * FROM TestSubmission t
CASE WHEN t.Region <> @region THEN
OR t.SUB_TYPE <> @subType
OR t.VENDOR_ID <> @vendorId
OR t.DELIVERY_TYPE <> @deliveryType
OR t.DELIVERY_METHOD <> @deliveryMethod
(
UPDATE SubmissionManagement
SET FileLoadStatus = 4
)
ELSE
(
UPDATE SubmissionManagement
SET FileLoadStatus = 1
)
END

Is there any possibility of working with results from something like this?:
SELECT *
FROM TestSubmission t
WHERE t.Region <> @region OR
t.SUB_TYPE <> @subType OR
t.VENDOR_ID <> @vendorID OR
t.DELIVERY_TYPE <> @deliveryType OR
t.DELIVERY_TYPE <> @deliveryMethod

Grateful for any assistance.

Thank you,
Bruce

 

[markros]

May be:

update SubmissionManagement SET FileLoadStatus  = case when exists (SELECT 1
FROM	TestSubmission t 
WHERE	t.Region <> @region OR 
t.SUB_TYPE <> @subType OR
t.VENDOR_ID <> @vendorID OR
t.DELIVERY_TYPE <> @deliveryType OR
t.DELIVERY_TYPE <> @deliveryMethod 
) then 4 else 1 end

PluralSight Learning Library

 

[Qik3Coder]

CousinBrucie,
You don't have a solid join between TestSubmission and SubmissionManagement, so you're going to get some very loose joining, especially using a wide not equals block like that.
Note, this block should affect ALL rows, because there is no separate filtering criteria, which would need to be applied to the end.

markos,
I don't think that is going to do what you wanted.
You are doing something akin to "set all marbles blue where 1 marble is red".
I'm sure you meant something like:

update [red]t2[/red]
SET FileLoadStatus  = case when exists 
	(SELECT 1
	FROM	TestSubmission t 
	WHERE	t.Region <> [red]t2.[/red]region OR 
	t.SUB_TYPE <> [red]t2.[/red]sub_Type OR
	t.VENDOR_ID <> [red]t2.[/red]vendor_ID OR
	t.DELIVERY_TYPE <> [red]t2.[/red]delivery_Type OR
	t.DELIVERY_[red]METHOD[/red] <> [red]t2.[/red]deliveryMethod 
) then 4 else 1 end
[red]FROM SubmissionManagement t2[/red]
WHERE 1=1
[green]--EXTRA FILTER CRITERIA HERE[/green]

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

 

[markros]

Yes, most likely this is what the OP wanted.

PluralSight Learning Library

 

[CousinBrucie]

Thank you both for your responses.

This stored procedure is going to take in the FileName from an SSIS package, with the prior steps being that both the Submission table will be truncated, so we'll always be sure that the detail records in the Submission table belong to the MAX record in the SubmissionManagement table. Let me try and implement the last code snippet and see what happens. I really appreciate this help.

CB

 

[CousinBrucie]

Hello, I've got this which is working without the WHERE clause (or when WHERE 1 = 1) and am trying to get this to work with the MAX(FileLoadDate) in the filter condition. I'm comparing to the variable values which is working fine, but when I try to run this with a WHERE clause with a nested SELECT here is the error:

An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

UPDATE SubmissionManagement
SET FileLoadDate =
CASE WHEN exists
(
SELECT 1
FROM TestSubmission t
WHERE t.Region <> @region OR
t.SUB_TYPE <> @subType OR
t.VENDOR_ID <> @vendorID OR
t.DELIVERY_TYPE <> @deliveryType OR
t.DELIVERY_METHOD <> @deliveryMethod
)
THEN 4
ELSE 1
END
FROM SubmissionManagement
WHERE (SELECT MAX(FileLoadDate))

Thanks,
CB

 

[markros]

Your WHERE clause makes no sense. Do you only want to update one row with the Max date? If so, try:

;with cte as (select top (1) FileLoadDate, FileLoadStatus,
CASE WHEN exists 
(
SELECT	 1 
FROM	TestSubmission t 
WHERE	t.Region <> @region OR 
t.SUB_TYPE <> @subType OR 
t.VENDOR_ID <> @vendorID OR 
t.DELIVERY_TYPE <> @deliveryType OR 
t.DELIVERY_METHOD <> @deliveryMethod 
) 
THEN 4 
ELSE 1 
END as NewStatus
FROM SubmissionManagement 
ORDER BY FileLoadDate DESC)

UPDATE cte SET FileLoadStatus = NewStatus

PluralSight Learning Library

 

[CousinBrucie]

Wow, thanks Mark. I see I have a lot to learn with this stuff, but what a great example I can learn from as I implement and test with this.
CB