Question on Update script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on Update script

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 13:43:25

Need to update a field in a table based off of a summary of another field.

update cicmpy
set cicmpy.number_field1 = ????
join oehdrhst on cicmpy.cus_no = oehdrhst.cus_no
where month(inv_dt)=month(currentdate) and year(inv_dt)=year(currentdate)

Table I'm updating from is oehdrhst. I want to sum total_sale_amt by cus_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 13:47:40

[code]update
set c.number_field1 = o.total_sale_amt
from cicmpy c
join (select sum(total_sale_amt) as total_sale_amt,cus_no from oehdrhst group by cus_no )o on c.cus_no = o.cus_no
where month(inv_dt)=month(currentdate) and year(inv_dt)=year(currentdate)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 14:08:09

When I run this I get incorrect syntax near the keywork 'on' Line 4.

update cicmpy
set cicmpy.numberfield1 = total_sale_amt
from cicmpy
join (select sum(total_sls_amt) as total_sale_amt,oehdrhst_sql.cus_no from oehdrhst_sql group by oehdrhst_sql.cus_no) on cicmpy.debcode = oehdrhst_sql.cus_no
where month(oehdrhst_sql.inv_dt)=month(currentdate) and year(oehdrhst_sql.inv_dt)=year(currentdate)

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 14:30:26

Seems visakh has gone to bed :)

Try this


update cicmpy
set cicmpy.number_field1 = (select sum(total_sale_amt) from oehdrhst o where o.cus_no=oehdrhst.cus_no)
join oehdrhst on cicmpy.cus_no = oehdrhst.cus_no
where month(inv_dt)=month(currentdate) and year(inv_dt)=year(currentdate)

PBUH

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 14:54:36

I have this script working kind of. I need it to only do the update where subExt_rebate is not equal NULL

update cicmpy
set cicmpy.numberfield1 = (select A.sumExt_rebate
from(select cus_no,sum(tot_sls_amt)
as sumExt_Rebate from oehdrhst_sql
group by cus_no)A
where A.cus_no = cicmpy.debcode and month(oehdrhst_sql.inv_dt) = month(getdate()) and year(oehdrhst_sql.inv_dt)=year(getdate())and a.sumext_rebate <> Null)
from oehdrhst_sql join cicmpy on oehdrhst_sql.cus_no = cicmpy.debcode

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 14:56:08

quote:
Originally posted by Vack

I have this script working kind of. I need it to only do the update where subExt_rebate is not equal NULL

update cicmpy
set cicmpy.numberfield1 = (select A.sumExt_rebate
from(select cus_no,sum(tot_sls_amt)
as sumExt_Rebate from oehdrhst_sql
group by cus_no)A
where A.cus_no = cicmpy.debcode and month(oehdrhst_sql.inv_dt) = month(getdate()) and year(oehdrhst_sql.inv_dt)=year(getdate())and a.sumext_rebate Is Not Null )
from oehdrhst_sql join cicmpy on oehdrhst_sql.cus_no = cicmpy.debcode

See the correction in red.

PBUH

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 14:58:29

That's what I thought too, but I am still getting:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'numberfield1', table '002.dbo.cicmpy'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 15:14:40

ok then try this


update cicmpy
set cicmpy.numberfield1 = (select A.sumExt_rebate
from(select cus_no,sum(tot_sls_amt)
as sumExt_Rebate from oehdrhst_sql
where sumext_rebate Is Not Null
group by cus_no)A
where A.cus_no = cicmpy.debcode and month(oehdrhst_sql.inv_dt) = month(getdate()) and year(oehdrhst_sql.inv_dt)=year(getdate()))
from oehdrhst_sql join cicmpy on oehdrhst_sql.cus_no = cicmpy.debcode

PBUH

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 15:38:11

Looks like I may have a different problem that may be causing my null issue. Using this code just looks at every order for a customer and updates the field with the Total.

update cicmpy
set cicmpy.numberfield1 = (select A.TotalSales
from(select cus_no,sum(tot_sls_amt)
as TotalSales from oehdrhst_sql
group by cus_no)A
where A.cus_no = cicmpy.debcode)
from oehdrhst_sql join cicmpy on oehdrhst_sql.cus_no = cicmpy.debcode

AS soon as I add
and month(oehdrhst_sql.inv_dt) = month(getdate()) and year(oehdrhst_sql.inv_dt)=year(getdate())

I get zero. I have 3 records in my table with an invoice date of 10/5/2010

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 15:44:47

You need to post the DDL & and the sample data of the tables involved in the query or else it will be like a shot in the dark from our side.

PBUH

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 16:10:23

How do I do that?

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 00:41:39

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-06 : 12:56:17

Here is the table.

/****** Object: Table [dbo].[oehdrhst_sql] Script Date: 10/06/2010 12:47:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[oehdrhst_sql](
[ord_type] [char](1) NOT NULL,
[ord_no] [char](8) NOT NULL,
[status] [char](1) NULL,
[entered_dt] [datetime] NULL,
[ord_dt] [datetime] NULL,
[apply_to_no] [char](8) NULL,
[oe_po_no] [char](25) NOT NULL,
[cus_no] [char](20) NOT NULL,
[bal_meth] [char](1) NULL,
[bill_to_name] [char](40) NULL,
[bill_to_addr_1] [char](40) NULL,
[bill_to_addr_2] [char](40) NULL,
[bill_to_addr_3] [char](40) NULL,
[bill_to_addr_4] [char](44) NULL,
[bill_to_country] [char](3) NULL,
[cus_alt_adr_cd] [char](15) NOT NULL,
[ship_to_name] [char](40) NULL,
[ship_to_addr_1] [char](40) NULL,
[ship_to_addr_2] [char](40) NULL,
[ship_to_addr_3] [char](40) NULL,
[ship_to_addr_4] [char](44) NULL,
[ship_to_country] [char](3) NULL,
[shipping_dt] [datetime] NULL,
[ship_via_cd] [char](3) NULL,
[ar_terms_cd] [char](2) NULL,
[frt_pay_cd] [char](1) NULL,
[ship_instruction_1] [char](40) NULL,
[ship_instruction_2] [char](40) NULL,
[slspsn_no] [int] NOT NULL,
[slspsn_pct_comm] [decimal](6, 3) NULL,
[slspsn_comm_amt] [decimal](16, 2) NULL,
[slspsn_no_2] [int] NULL,
[slspsn_pct_comm_2] [decimal](6, 3) NULL,
[slspsn_comm_amt_2] [decimal](16, 2) NULL,
[slspsn_no_3] [int] NULL,
[slspsn_pct_comm_3] [decimal](6, 3) NULL,
[slspsn_comm_amt_3] [decimal](16, 2) NULL,
[tax_cd] [char](3) NULL,
[tax_pct] [decimal](6, 4) NULL,
[tax_cd_2] [char](3) NULL,
[tax_pct_2] [decimal](6, 4) NULL,
[tax_cd_3] [char](3) NULL,
[tax_pct_3] [decimal](6, 4) NULL,
[discount_pct] [decimal](5, 2) NULL,
[job_no] [char](20) NULL,
[mfg_loc] [char](3) NULL,
[profit_center] [char](8) NULL,
[dept] [char](8) NULL,
[ar_reference] [char](45) NULL,
[tot_sls_amt] [decimal](16, 2) NULL,
[tot_sls_disc] [decimal](16, 2) NULL,
[tot_tax_amt] [decimal](16, 2) NULL,
[tot_cost] [decimal](16, 2) NULL,
[tot_weight] [decimal](10, 3) NULL,
[misc_amt] [decimal](16, 2) NULL,
[misc_mn_no] [char](9) NULL,
[misc_sb_no] [char](8) NULL,
[misc_dp_no] [char](8) NULL,
[frt_amt] [decimal](16, 2) NULL,
[frt_mn_no] [char](9) NULL,
[frt_sb_no] [char](8) NULL,
[frt_dp_no] [char](8) NULL,
[sls_tax_amt_1] [decimal](16, 2) NULL,
[sls_tax_amt_2] [decimal](16, 2) NULL,
[sls_tax_amt_3] [decimal](16, 2) NULL,
[comm_pct] [decimal](4, 2) NULL,
[comm_amt] [decimal](16, 2) NULL,
[cmt_1] [char](35) NULL,
[cmt_2] [char](35) NULL,
[cmt_3] [char](35) NULL,
[payment_amt] [decimal](16, 2) NULL,
[payment_disc_amt] [decimal](16, 2) NULL,
[chk_no] [char](8) NULL,
[chk_dt] [datetime] NULL,
[cash_mn_no] [char](9) NULL,
[cash_sb_no] [char](8) NULL,
[cash_dp_no] [char](8) NULL,
[ord_dt_picked] [datetime] NULL,
[ord_dt_billed] [datetime] NULL,
[inv_no] [char](8) NOT NULL,
[inv_dt] [datetime] NULL,
[selection_cd] [char](1) NULL,
[posted_dt] [datetime] NULL,
[part_posted_fg] [char](1) NULL,
[ship_to_freefrm_fg] [char](1) NULL,
[bill_to_freefrm_fg] [char](1) NULL,
[copy_to_bm_fg] [char](1) NULL,
[edi_fg] [char](1) NULL,
[closed_fg] [char](1) NULL,
[accum_misc_amt] [decimal](16, 2) NULL,
[accum_frt_amt] [decimal](16, 2) NULL,
[accum_tot_tax_amt] [decimal](16, 2) NULL,
[accum_sls_tax_amt] [decimal](16, 2) NULL,
[accum_tot_sls_amt] [decimal](16, 2) NULL,
[hold_fg] [char](1) NULL,
[prepayment_fg] [char](1) NULL,
[lost_sale_cd] [char](3) NULL,
[orig_ord_type] [char](1) NULL,
[orig_ord_dt] [datetime] NULL,
[orig_ord_no] [char](8) NULL,
[award_probability] [tinyint] NULL,
[oe_cash_no] [char](8) NULL,
[exch_rt_fg] [char](1) NULL,
[curr_cd] [char](3) NULL,
[orig_trx_rt] [decimal](11, 6) NULL,
[curr_trx_rt] [decimal](11, 6) NULL,
[tax_sched] [char](5) NULL,
[user_def_fld_1] [char](30) NULL,
[user_def_fld_2] [char](30) NULL,
[user_def_fld_3] [char](30) NULL,
[user_def_fld_4] [char](30) NULL,
[user_def_fld_5] [char](30) NULL,
[deter_rate_by] [char](1) NULL,
[form_no] [tinyint] NULL,
[tax_fg] [char](1) NULL,
[sls_mn_no] [char](9) NULL,
[sls_sb_no] [char](8) NULL,
[sls_dp_no] [char](8) NULL,
[ord_dt_shipped] [datetime] NULL,
[tot_dollars] [decimal](16, 2) NULL,
[mult_loc_fg] [char](1) NULL,
[tot_tax_cost] [decimal](16, 2) NULL,
[hist_load_record] [char](1) NULL,
[pre_select_status] [char](1) NULL,
[packing_no] [int] NULL,
[deliv_ar_terms_cd] [char](2) NULL,
[inv_batch_id] [char](8) NULL,
[bill_to_no] [char](20) NULL,
[rma_no] [char](8) NULL,
[prog_term_no] [int] NULL,
[extra_1] [char](1) NULL,
[extra_2] [char](1) NULL,
[extra_3] [char](1) NULL,
[extra_4] [char](1) NULL,
[extra_5] [char](1) NULL,
[extra_6] [char](8) NULL,
[extra_7] [char](8) NULL,
[extra_8] [char](12) NULL,
[extra_9] [char](12) NULL,
[extra_10] [decimal](16, 6) NULL,
[extra_11] [decimal](16, 6) NULL,
[extra_12] [decimal](16, 2) NULL,
[extra_13] [decimal](16, 2) NULL,
[extra_14] [int] NULL,
[extra_15] [int] NULL,
[edi_doc_seq] [smallint] NULL,
[contact_1] [char](100) NULL,
[phone_number] [char](25) NULL,
[fax_number] [char](25) NULL,
[email_address] [char](128) NULL,
[use_email] [char](10) NULL,
[ship_to_city] [char](100) NULL,
[ship_to_state] [char](3) NULL,
[ship_to_zip] [char](20) NULL,
[bill_to_city] [char](100) NULL,
[bill_to_state] [char](3) NULL,
[bill_to_zip] [char](20) NULL,
[filler_0001] [char](146) NULL,
[hist_dt] [datetime] NULL,
[hist_tm] [datetime] NULL,
[user_name] [char](20) NULL,
[user_namex] [char](20) NULL,
[id_no] [char](50) NOT NULL,
[jnl_src] [char](6) NULL,
[batch_id] [char](10) NULL,
[trx_posted_fg] [char](1) NOT NULL,
[ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

Is there a way to upload the exported table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources