update query

Question

Post reply

update query

luissantos

SSCertifiable

Points: 7391
More actions
March 21, 2004 at 12:13 pm

#110664

Hello
I have the following TSQL statment:
update sl set sl.fistamp=fi.fistamp,sl.origem='FT',sl.slstamp=fi.fistamp
from sl inner join fi on cast(sl.adoc as bigint(18))=fi.fno and sl.ref=fi.ref
where sl.origem='SL' and
(sl.cm=75 or sl.cm=76 or sl.cm=77 or sl.cm=78) and year(sl.datalc)=2003
sl = my table to keep lines of invoices ; slstamp = is Primery Key;Adoc= my invoice nº in table SL and FI.FNO is my invoice nº on the lines; sl.ref or fi.ref = my article; sl.cm = my code of movment of inventory (entry or exit code); fistamp is unique for each article line in my invoice line table.
when i run this code my query return always "Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'pk_sl'. Cannot insert duplicate key in object 'sl'.
The statement has been terminated."
But when i run the following statment :
SELECT fi.fistamp
FROM fi
WHERE exists (SELECT fistamp FROM fi As Tmp GROUP BY fistamp HAVING Count(*)>1 ) and fi.ref='30T155.S'
ORDER BY fi.fistamp
query analiser return zero values what means that i have no duplicates on my field FISTAMP.
What i want to know if i can , when i run the update statment, make like a debbuger to returns me, what the ADOC(nº of my invoice or what my article where the error occurs, because is a very larger table.
Many thanks in advance
Luis Santos

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

I would use same logic in update statement to check.

Try this

select sl.slstamp, fi.fistamp

from sl inner join fi on cast(sl.adoc as bigint(18))=fi.fno and sl.ref=fi.ref

where sl.origem='SL' and

(sl.cm=75 or sl.cm=76 or sl.cm=77 or sl.cm=78) and year(sl.datalc)=2003

order by 1,2

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Your query to find duplicate fistamp on the fi table is incorrect. You are looking on the fi table when you should be checking the sl table (look at your error message).

You are trying to update the sl table with information from the fi table. Okay so far, but at least ONE of the fistamps already exists in the sl table. That's what you are being told by the error message.

So, yes there aren't any duplicates on the fi table, but at least one of the fistamp already exists in the sl table.

-SQLBill

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Thanks for yours replies

I discovered that the problem is not on my table fi because FISTAMP in this table has no duplicates.

The problem is when i have the same REF twice on the same document and although FISTAMP is different, i can´t make the update using this WHERE clause "cast(sl.adoc as int(18))=fi.fno and sl.ref=fi.ref " because my query doesn´t know which line in the table to use for the REF to obtain the correct FISTAMP,always returning the first ref and it´s FISTAMP.

I must find another field to join the tables. The unique solution maybe is to create a cursor to scroll by document lines for each ref with my where condition "cast(sl.adoc as int(18))=fi.fno and sl.ref=fi.ref " or something else.

Many thanks

Luis Santos