Update statement on join

[onpnt]

Hi all, I have a update statement I just can't seem to sort out. I'm a sql server native so this has been a struggle. I did some searching for similar statements and the syntax for doing it but haven't found much.

Basically I have to update records based on duplicate values found. The actual statement isn't that different to query but the update is killing me.

Here is what I have

UPDATE	vendor_cost vc
INNER JOIN
(
SELECT	
v_id,vi_id,cost_zone, store_id,MAX(vc_start_date) AS MAX_vc_start_date
FROM	vendor_cost
WHERE	vc_end_date is NULL 
GROUP	BY v_id, vi_id, cost_zone, store_id
HAVING	COUNT(v_id) > 1
) A 
INNER JOIN vendor_cost vc
	ON	A.v_id = vc.v_id
	AND	 A.vi_id = vc.vi_id
	AND	 A.cost_zone = vc.cost_zone
	AND	A.store_id = vc.store_id
SET vc_end_date = A.MAX_vc_start_date - 1 Day	
WHERE	
  vc_end_date is NULL 
	AND	
	vc.vc_start_date < A.MAX_vc_start_date

I'm still reading up the documentation on DB2 so hopefully I'll find an answer but any input would be very appreciated if you see a big shinny mistake (which I'm sure there is)

A working T-SQL version is

UPDATE dbo.vendor_cost  
SET vc_end_date = A.MAX_vc_start_date-1
FROM dbo.vendor_cost vc  
INNER JOIN (
SELECT 
v_id,vi_id,cost_zone, 
store_id,
MAX(vc_start_date) AS MAX_vc_start_date
FROM dbo.vendor_cost
where vc_end_date is null 
group by v_id, vi_id, cost_zone, store_id
having count(v_id) > 1
) A 
on A.v_id = vc.v_id and  A.vi_id = vc.vi_id and  
A.cost_zone = vc.cost_zone and A.store_id = vc.store_id
where vc_end_date is null 
and vc.vc_start_date <> A.MAX_vc_start_date

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[MarcLodge]

onpnt,
What error does your code give?

 

[onpnt]

Hello MarcLodge,

Apologies, thought I added it.

It gives me a expected token on JOIN line 2

Here --> INNER JOIN

I'll get the word for word error once I get my other machine up with the tera client on it.

Thanks for the assistance

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

Hope everyones weekends were good to them

My exact error is actually different than above. I was thinking of the previous attempt at formatting correctly

An unexpected toket "SET" was found following "ore_id = vc.store_id". Expected tokens may include: "ON"

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[blom0344]

IMHO the T-sql syntax is following the ISO/ANSI standard which is supported by DB2 as well.
The SET operator should follow the preceding UPDATE and not be situated 'somewhere' in the script.
Did you try the T-SQL version in DB2?

Ties Blom

 

[onpnt]

Yes I tried it. It was the first one I did. That's what I thought as well which is kind of why I just wrote it using my local instance of sql server to test.

Here is the small edited version from the t-sql

UPDATE vendor_cost  
SET vc_end_date = A.MAX_vc_start_date - 1 DAY
FROM vendor_cost vc  
INNER JOIN (
SELECT 
v_id,vi_id,cost_zone, 
store_id,
MAX(vc_start_date) AS MAX_vc_start_date
FROM vendor_cost
where vc_end_date is null 
group by v_id, vi_id, cost_zone, store_id
having count(v_id) > 1
) A 
on A.v_id = vc.v_id and  A.vi_id = vc.vi_id and  
A.cost_zone = vc.cost_zone and A.store_id = vc.store_id
where vc_end_date is null 
and vc.vc_start_date <> A.MAX_vc_start_date

Error: An unexpected token "FROM vendor_cost vc INNER JOIN" was found following "c_start_date - 1 DAY"

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[blom0344]

Assuming no correlation is attempted:

UPDATE vendor_cost  
SET vc_end_date = 
SELECT A.MAX_vc_start_date - 1 DAY
FROM 
(vendor_cost vc  
INNER JOIN (
SELECT 
v_id,vi_id,cost_zone, 
store_id,
MAX(vc_start_date) AS MAX_vc_start_date
FROM vendor_cost
where vc_end_date is null 
group by v_id, vi_id, cost_zone, store_id
having count(v_id) > 1
) A 
on A.v_id = vc.v_id and  A.vi_id = vc.vi_id and  
A.cost_zone = vc.cost_zone and A.store_id = vc.store_id
where vc_end_date is null 
and vc.vc_start_date <> A.MAX_vc_start_date)

Ties Blom

 

[onpnt]

Thanks blom0344

I'm getting a Unexpected token "A" in the

SET vc_end_date =
SELECT A.MAX_vc_start_date - 1 DAY

Line


_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

Looks like I needed ( ) around the entire SELECT. I remembered from reading up on the UPDATE syntax.

It's running. Thanks and I'll post back the results

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

blom0344 Thanks again for the help but after looking at it and running through it won't go either. The sub query is returning multiple rows so I can't structure it that way.

Error: The result of a scalar fullselect, select into statement, or values into statement is more than one row.

Frustrating after working in T-SQL so long not to get this what should be simple one running

I have a feeling I'll be smacking myself when we find what my mistake is (or all of you will be smacking me ;-) )

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[blom0344]

The problem is that you need the proper correlation between what is returned from the subset and the record that you want to update.
I assume that you do not want to update all records with the same value of A.MAX_vc_start_date - 1 DAY?
It looks as though in case of the T-SQL some sort of implicit correlation is executed that should be explicitly stated for DB2.
I have not working instance of DB2 anymore, so can someone else work out the proper syntax?

Ties Blom

 

[onpnt]

Got it. I think the only way this is going to work is possibly utilizing temp tables. I made a work around but need (fro my own sanity) to get this one going. I got it to work in PL last night also and it seems DB2 is the only one that hates me

Yes all the fields are being updated to A.MAX_vc_start_date - 1 DAY aside from the highest found vc_start_date. It really doesn't matter what the vc_end_date is as long as the value is less than the highest vc_start_date. This is a maintenance job based on a leak in the original programming. (go figure)



_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}