September 21, 2005 at 11:43 am
Hi all, I thought it is a small problem but killed a lot of time so, it is here atlast.
I need to update a table called N_Order and the conditions are like this.
If N_Order.id = N_Inv.id and any one of the following is true
1.N_Inv.TF is not null
2.Record exists in Bank Table where Debtkey =(select Debtkey from N_Inv)
The thing is I need to check for each and every record, whether debtkey exists in the Bank Table or not.
Any help will be appreciated. Thanks
September 21, 2005 at 12:02 pm
You haven't said what you want to update and what value, so I have left these for you to complete.
update no
set [field] = [whatever]
from n_order no join n_inv ni on no.id = ni.id
join BankTable bt on ni.debtkey = bt.debtkey
I do not understand what you mean by 'check each and every record' - please explain further.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 21, 2005 at 12:04 pm
exactly update what column(s) to what values where this criteria is true.
If you can make a select statement out of your rules. then use the from, join where clause with an update statement.
Post a select statement that returns rows you want to change,
the column and the value you want it to be, and we can help.
September 21, 2005 at 12:15 pm
Ok, this is what I need to update
update N_Order set N_Order.status = 2
If N_Order.id = N_Inv.id and any one of the following is true
(N_Inv.TF is not null or if there is a Record exists in Bank Table where Debtkey =(select Debtkey from N_Inv)). I hope Iam clear now
Any help will be appreciated. Thanks
September 21, 2005 at 12:44 pm
These are the tables I am working with
CREATE TABLE [NAT_WORKORDER_DETAIL] (
[ATTORNEY_PLACEMENT_DATE] [smalldatetime] NULL ,
[AGYID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS_FLAG] [decimal](18, 0) NULL ,
[DAYS_OVERDUE] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
------------------------------------------------------
CREATE TABLE [NAT_INVENTORY] (
[T_Debt_key] [decimal](9, 0) NOT NULL ,
[T_Agyid] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[T_IE] [datetime] NULL ,
[T_PIF_SIF] [datetime] NULL ,
[T_AGING_Enforce_Judgments_Date] [datetime] NULL
) ON [PRIMARY]
GO
------------------------------------------------------------
CREATE TABLE [BANKRUPT] (
[DEBT_KEY] [numeric](9, 0) NOT NULL ,
[ATTY_KEY] [numeric](9, 0) NOT NULL ,
[TRUSTEE_KEY] [numeric](9, 0) NOT NULL
) ON [PRIMARY]
GO
---------------------------------------------------------------
CREATE TABLE [DEBT] (
[DEBT_KEY] [numeric](9, 0) NOT NULL ,
[AGYID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CANC_BY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SENT_DATE] [datetime] NOT NULL
) ON [PRIMARY]
GO
And this is what I need to update.
If Nat_WorkOrder_Detail.Agyid = Nat_Inventory.T_Agyid
And
Any one of the following is true:
Nat_Inventory.T_PIF_SIF is not null OR
Record exists in Bankrupt table where Debt_Key = (Debt.Debt_Key where Nat_WorkOrder_Detail.Agyid = Debt. Agyid )
Then Set Nat_WorkOrder_Detail.Status_Flag = ‘2’
September 21, 2005 at 12:54 pm
update no
set status = 2
from n_order no join n_inv ni on no.id = ni.id
where (ni.tf is not null or exists (select bt.debtkey from banktable bt where bt.debtkey = ni.debtkey))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply