August 3, 2009 at 11:45 am
Hi,
I need help in defining my criteria in the below statement.
Update Table A
SET b.fbitdeleted = 0
WHERE PKcolumn IN
(
SELECTPKcolumn
FROMTableA a
INNER JOINTable b
ONb.ItemNo=a.Skunum and b.IdNo=a.Iduse
--WHEREb.fbitDeleted = 1 AND a.Price > 0 a.ItemNo LIKE '%FB%' OR a.Status = 'V1'
)
I'm joining two tables based on two unique keys. Once joined, I get the result set and I want to set the fbitdeleted column to 0 if any of the criteria is as defined below:
Fbitdeleted is 1
Price is greater than 0
Itemno contains the characters FB
Status is V1
Will appreciate the help.
Thanks,
S
--
:hehe:
August 3, 2009 at 12:02 pm
Which table are you trying to update, tableA or TableB. It is really hard to tell from the code you posted.
August 3, 2009 at 12:40 pm
Aah... you know what.. I think I have this figured out. I was trying to update Table B above, I just used AND's in the criteria and then added another update statement next which catches all the non qualifying records and sets them to fbitdeleted = 1.
I tried to delete this post but was unsuccessful. It almost seemed like I needed to write this down to figure it out. Thanks for the helping hand anyway.
--
:hehe:
August 3, 2009 at 12:49 pm
Slick84 (8/3/2009)
Aah... you know what.. I think I have this figured out. I was trying to update Table B above, I just used AND's in the criteria and then added another update statement next which catches all the non qualifying records and sets them to fbitdeleted = 1.I tried to delete this post but was unsuccessful. It almost seemed like I needed to write this down to figure it out. Thanks for the helping hand anyway.
One small piece of advice: you're mixing ANDs and ORs in your criteria. This can get VERY confusing when you develop in a hurry, because the operator precedence is not clear, so you really want to make sure to include parentheses to make sure the logic you want is the logic the engine interprets....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 3, 2009 at 1:05 pm
That's true Matt. That was exactly the reason that threw me off and got me confused. So I did use parentheses in my next statement. In the first update I set the criteria using AND so that all 4 criteria are met.
--
:hehe:
August 7, 2009 at 2:47 am
dgfghgf
August 7, 2009 at 6:36 am
ramya.he (8/7/2009)
dgfghgf
Thanks for your insightful reply. Exactly the SQL syntax I was looking for. Works like magic under my where clause! 😎
--
:hehe:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply