Update query error
-
Hi guys,
I have 2 tables with the same fields, one is a temp version which i used to update the other with recent records.
Only when i use the following SQL
UPDATE Ticketing SET TicketPriceExVat =
(SELECT TicketPriceExVat FROM TempTicketing
WHERE TempTicketing.EventID = Ticketing .EventID)
it updates the ones i want to, and nulls all other values.
Any thoughts?
Alex
-
this will update all rows ALWAYS, there's no where condition to filter the rows to update, so if no value is found in TicketPriceExVat then a null will be "inserted"
if you'd wrap an isnull around you might be ok :
ISNULL((SELECT TicketPriceExVat FROM TempTicketing
WHERE TempTicketing.EventID = Ticketing .EventID), TicketPriceExVat )
but that would still update all rows regardless of a new value is found or not...
Is this a one time process or this is going in production for good?
-
Seems to me you should add another clause to this:
UPDATE Ticketing SET TicketPriceExVat =
(SELECT TicketPriceExVat FROM TempTicketing
WHERE TempTicketing.EventID = Ticketing .EventID
AND TicketPriceExVat IS NOT NULL)
**ASCII stupid question, get a stupid ANSI !!!** -
OOPS - Like Ron K. said yesterday - should learn to type faster!
**ASCII stupid question, get a stupid ANSI !!!** -
R u sure this would work??
Looks like you're returning a null anyways if not match is found...
-
Remi- hi again!
hmm annoying about the update thing. didnt realise.
not too sure this would work as i can't go on null values. basicalyl the full ticketing table contains details on ticket prices, tickets sold etc for all events. i only want to update thenm (with the values in tempticketing) for events that have not finished. these are the only events that will need updating as the finished ones will not be having tickets sold for them.
i guess i could take the tempticketing table, insert all rows from ticketing where ticketing.eventid not in tempticketing.eventid. then drop ticketing, recreate ticketing and insert all these values?
this would then give me all the values from tempticketing plus all the others in ticketing that do not have records in tempticketing.
cant think of a better way really?
Alex
-
You can always make a select of the tickets that need to be updated. Once you have that you can do something like this :
UPDATE T SET T.TicketPriceExVat = dtNewPrices.TicketPriceExVat
FROM dbo.Ticketing T inner join
(Select EventId, TicketPriceExVat from dbo.WhatEver) dtNewPrices
on T.EventId = dtNewPrices.EventId
-
Cheers Remi, this is a lot neater way of doing things!
Alex
-
Remi - apologies for the delay (that darn thing called work keeps interrupting) - yes, you're right (surprise! surprise!) - that does return all the nulls...
I was confusing it with an article on joins that I read recently - it should actually have been:
UPDATE Ticketing
SET TicketPriceExVat =
TempT.TicketPriceExVat FROM TempTicketing TempT
INNER JOIN Ticketing T ON TempT.EventID = T.EventID
AND TempT.TicketPriceExVat IS NOT NULL
**ASCII stupid question, get a stupid ANSI !!!** -
Not sure you need the not null... cause if there's no price then there's no row so the join won't update the line at all.
-
Remi - my response was only to correct the update that I originally posted - not a solution to Alex's problem (which in any case you have resolved in your own SUPERMAN way...<;-)
**ASCII stupid question, get a stupid ANSI !!!** -
Ok... but I preffer being referred as Neo or Luke
. -
May the force be with you
* Noel -
Ya gonna need it... 3 days week-end here.
So it's time to log off now.
-
Enjoy the break!
Long live the Queen.....(or at least holidays in her name!!!)
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply