February 2, 2011 at 3:43 pm
I wrote the following query:
UPDATE Unternehmen
SET Loeschvormerkung = N'Something ' + Unternehmen.Loeschvormerkung
FROM Unternehmen RIGHT OUTER JOIN
LoeschvormerkungenK ON Unternehmen.Verkehrsnummer = LoeschvormerkungenK.KdNrVormerkung
It works fine, but not, if there are multiple records with the same Unternehmen.Verkehrsnummer. IN that case it semms to update only the first occurrence of that Unternehmen.Verkehrsnummer in the table Unternehmen.
I tried the same with a SELECT and it showed all records including the ones, that had the same value in Unternehmen.Verkehrsnummer.
Interesting detail: SELECT shows 290 records and the UPDATE Query says it changed 290 records and it says that 3 times...
I tried to modify the query like that:
UPDATE Unternehmen
SET Loeschvormerkung = N'Something '+Loeschvormerkung
FROM Unternehmen RIGHT JOIN
LoeschvormerkungenK ON Unternehmen.Verkehrsnummer = LoeschvormerkungenK.KdNrVormerkung
WHEREUnternehmen.Verkehrsnummer IN (SELECT KdNrVormerkung FROM LoeschvormerkungenK)
This had no effect.
Now I have no idea left...Can someone here help me with that?
Thanks.
February 2, 2011 at 4:09 pm
Can you please explain what you are trying to achieve here?
I ask because currently you have an update on a table that is the LEFT OUTER part of a join, which is not right.
I won't post any code because I am not 100% sure of the desire effect.
Let me guess:
You want to prefix the Loeschvormerkung column in Unternehmen with something whenever Unternehmen.Verkehrsnummer is present in
the KdNrVormerkung column in LoeschvormerkungenK
Is that right?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 2, 2011 at 4:25 pm
Thank you for the fast reaction. Yes, I think you understood the problem I am trying to solve.
In my words:
There is a list of numbers KdNrVormerkung in the table LoeschvormerkungenK. KdNrVormerkung is a number, that has an equivalent in Unternehmen with the name Verkehrsnummer. There can be multiple records with the same Verkehrsnummer. There are 313 records in KdNrVormerkung and more than 10000 in Unternehmen. 290 of the 10000 in Unternehmen should get should get a
note in Loeschvormerkung by keeping any existing notes. Not all of the 313 records in KdNrVormerkung are present in Unternehmen and there are some, that are there more than once...
February 2, 2011 at 4:38 pm
Try this form:
The OUTPUT is just to show you what gets UPDATED and can be removed once you are happy.
The BEGIN TRAN and ROLLBACK are just in case it does the wrong thing 😛
BEGIN TRAN
UPDATE Unternehmen
SET Loeschvormerkung = N'Something ' + Unternehmen.Loeschvormerkung
OUTPUT INSERTED.Verkehrsnummer,INSERTED.Loeschvormerkung,DELETED.Loeschvormerkung
WHERE EXISTS (SELECT 1 FROM LoeschvormerkungenK WHERE Unternehmen.Verkehrsnummer = LoeschvormerkungenK.KdNrVormerkung)
ROLLBACK
Edit: Moved OUTPUT clause to the correct place - doh!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 2, 2011 at 4:56 pm
Thanks for the code.
I tried your code with Output, but it didn't work. I tried with the Output after the SET, which brought the following message: The target table 'Unternehmen' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
The I tried the code without the Output and teh result still remains the same.
It works great in SELECT, but not the way I would expect with UPDATE... 🙁
And still I have the message 3 times like this:
(290 row(s) affected)
(290 row(s) affected)
(290 row(s) affected)
February 2, 2011 at 4:58 pm
Sorry , my mistake - I did indeed put the OUPUT clause in the wrong place - must be time to sleep!
So, you have triggers on there the - that could be the problem - try disabling them before the update then re-enable them after - unless their function should be included in this update - in which case we need to see the triggers.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 2, 2011 at 5:12 pm
Hmmm...
I indeed forgot about the trigger, which is keeping the history of all changes in another table...
But the problem seems to be here:
SET Loeschvormerkung = N'Something ' + Unternehmen.Loeschvormerkung
The Output without the trigger now works and looks like this:
...
51504NULLNULL
40938NULLNULL
40959NULLNULL
31620NULLNULL
28621Something Dublette von 5060
...
So it didn't put 'Something' into the filed with the NULL...And I have no idea why...:-(
February 2, 2011 at 5:16 pm
Well, depending on your SETtings concatenating a NULL with a string yields a NULL.
If you want to change NULLS to 'Something' then use N'Something '+COALESCE(Unternehmen.Loeschvormerkung,'')
This will ensure you always get a non-NULL result
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 2, 2011 at 5:28 pm
Thank you very much. This was the problem. But two more questions: Why should NULL + Something be NULL instead of Something? Where is the setting for that?
February 2, 2011 at 5:52 pm
Because NULL is an undefined value (think infinity or dividing by 0).
IF you take an undefined value and perform an operation on it - how can it be anything but undefined still?
Unless.....
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'test'+NULL; -- suddenly NULL is treated like '' ! Yuk!
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'test'+NULL;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply