Update statement help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Update statement help

Author

Topic

Trilom
Starting Member

6 Posts

Posted - 2012-05-05 : 13:50:07

Hi there. I am trying to write a recursive statement that will take possibly multiple values from possibly multiple rows from one table, and add them together, and put them on a row in another table.

Table 1 has a 'memid' that is not unique, I need all values from tblarrepetitive.price where the memid matches the memid on the 2nd table.

As I said, table 1 can contain only 1 value, but some have 2, 3, 4 or even more values, that all share the same memid.

Here is what I have so far, but it is only grabbing the first value from the corresponding memid:

update load_monroeville
set mem_rate = (mem_rate + r.price)
from load_monroeville l, tblarrepetitive r
where l.mnum = r.memid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 14:14:20

sorry not fully clear on your scenario. Please post data in below format and we'll help

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

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

Trilom
Starting Member

6 Posts

Posted - 2012-05-05 : 14:22:15

Source table:

CREATE TABLE [dbo].[tblartrans](
	[transid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tblartrans_transid]  DEFAULT (newid()),
	[transdate] [datetime] NOT NULL,
	[datepost] [datetime] NOT NULL,
	[arid] [smallint] NOT NULL,
	[memid] [uniqueidentifier] NULL,
	[site] [smallint] NOT NULL,
	[poslocation] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[employeeid] [uniqueidentifier] NULL,
	[invoice] [int] NOT NULL,
	[batchnum] [int] NOT NULL,
	[description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[amountinvoice] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountinvoice]  DEFAULT ((0)),
	[amountpaid] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountpaid]  DEFAULT ((0)),
	[amountbalance] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountbalance]  DEFAULT ((0)),
	[poa] [bit] NOT NULL CONSTRAINT [DF_tblartrans_payment]  DEFAULT ((0)),
	[amountminbill] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountminbill]  DEFAULT ((0)),
	[void] [bit] NOT NULL CONSTRAINT [DF_tblartrans_void]  DEFAULT ((0)),
	[billed] [bit] NOT NULL CONSTRAINT [DF_tblartrans_billed]  DEFAULT ((0)),
	[statementlogid] [uniqueidentifier] NULL,
	[billedfromid] [uniqueidentifier] NULL,
	[billedtoid] [uniqueidentifier] NULL,
	[postedtogl] [bit] NOT NULL CONSTRAINT [DF_tblartrans_postedtogl]  DEFAULT ((0)),
	[postlogid] [uniqueidentifier] NULL,
	[loginuser] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[pcname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[comment] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[referenceid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[repetitiveid] [uniqueidentifier] NULL,
	[coaar] [int] NULL,
	[refsite] [smallint] NULL,
	[taxid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[saleorigin] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csirowid] [bigint] IDENTITY(1,1) NOT NULL

Destination:

CREATE TABLE [dbo].[tblartrans](
	[transid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tblartrans_transid]  DEFAULT (newid()),
	[transdate] [datetime] NOT NULL,
	[datepost] [datetime] NOT NULL,
	[arid] [smallint] NOT NULL,
	[memid] [uniqueidentifier] NULL,
	[site] [smallint] NOT NULL,
	[poslocation] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[employeeid] [uniqueidentifier] NULL,
	[invoice] [int] NOT NULL,
	[batchnum] [int] NOT NULL,
	[description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[amountinvoice] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountinvoice]  DEFAULT ((0)),
	[amountpaid] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountpaid]  DEFAULT ((0)),
	[amountbalance] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountbalance]  DEFAULT ((0)),
	[poa] [bit] NOT NULL CONSTRAINT [DF_tblartrans_payment]  DEFAULT ((0)),
	[amountminbill] [smallmoney] NOT NULL CONSTRAINT [DF_tblartrans_amountminbill]  DEFAULT ((0)),
	[void] [bit] NOT NULL CONSTRAINT [DF_tblartrans_void]  DEFAULT ((0)),
	[billed] [bit] NOT NULL CONSTRAINT [DF_tblartrans_billed]  DEFAULT ((0)),
	[statementlogid] [uniqueidentifier] NULL,
	[billedfromid] [uniqueidentifier] NULL,
	[billedtoid] [uniqueidentifier] NULL,
	[postedtogl] [bit] NOT NULL CONSTRAINT [DF_tblartrans_postedtogl]  DEFAULT ((0)),
	[postlogid] [uniqueidentifier] NULL,
	[loginuser] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[pcname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[comment] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[referenceid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[repetitiveid] [uniqueidentifier] NULL,
	[coaar] [int] NULL,
	[refsite] [smallint] NULL,
	[taxid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[saleorigin] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csirowid] [bigint] IDENTITY(1,1) NOT NULL

I need the prices added from the source, where the memid is the same.

For example, memid is '123', there are two rows with memid of '123' but have prices '24' and '25' in separate rows.
I need a statement that adds those two prices, and puts them in one row on the destination, 'mem_rate'.

The source might have only memid row, but it also might have 2+ for each memid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 14:33:20

do you mean your source and destination are same?
where's the sample data and output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Trilom
Starting Member

6 Posts

Posted - 2012-05-05 : 14:44:25

Sample Data:
memid price
897EAC1B-300E-45D1-8432-0AB00E751861 75.00
8984D705-07C8-4CF6-8C4A-0AD0C627A4DC 15.00
8984D705-07C8-4CF6-8C4A-0AD0C627A4DC 25.00
8984D705-07C8-4CF6-8C4A-0AD0C627A4DC 147.00
825C2C33-5AB0-45DE-AA84-0AE1BE38783A 107.00
4E037CA4-7A99-45B7-A22F-0AED1A6C8FC6 147.00
E4E5117E-01FF-4C7C-A73E-0B108315E3C5 42.00
72135298-5EFE-4CEB-9FDB-0B7D8FA9846F 100.00
72135298-5EFE-4CEB-9FDB-0B7D8FA9846F 22.00
AD54A1D5-D508-4EC6-A3C8-0BA9EA30542F 75.00

desired output:
memid price
897EAC1B-300E-45D1-8432-0AB00E751861 75.00
8984D705-07C8-4CF6-8C4A-0AD0C627A4DC 187.00
825C2C33-5AB0-45DE-AA84-0AE1BE38783A 107.00
4E037CA4-7A99-45B7-A22F-0AED1A6C8FC6 147.00
E4E5117E-01FF-4C7C-A73E-0B108315E3C5 42.00
72135298-5EFE-4CEB-9FDB-0B7D8FA9846F 122.00
AD54A1D5-D508-4EC6-A3C8-0BA9EA30542F 75.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 15:31:24

You still didnt answer my question whether source and destination table are same. so i'm assuming they're in different db at least


UPDATE t
SET t.price = s.price
FROM  [dbo].[tblartrans] t
INNER JOIN
(
SELECT memid,SUM(price) AS price
FROM [sourcedb].[dbo].[tblartrans]
GROUP BY memid
)s
ON s.memid = t.memid

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

Trilom
Starting Member

6 Posts

Posted - 2012-05-05 : 18:02:46

I'm sorry, I've explained this horribly, but you're answer has led me to a solution. Thank you

	UPDATE l
	SET l.mem_rate = s.price
	FROM load_monroeville l
	INNER JOIN
	(
	SELECT memid, SUM(price) AS price
	FROM tblarrepetitive
	GROUP BY memid
	)s
	ON s.memid = l.mnum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 18:08:48

wc

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

Trilom
Starting Member

6 Posts

Posted - 2012-05-06 : 18:37:27

Again, thanks for the last issue. But I do have a new one now:

I have information coming from one table, to another table, in the same database. The source table has multiple records from one primary key, for example, '383b0cbf-d712-49d2-a05c-ed11f39732b0' has 24 records, I'm looking to insert these records into the new table with a column numbering each record by each primary key. Here is an example of the source:

10/1/2005 12:00:00 AM 383b0cbf-d712-49d2-a05c-ed11f39732b0
11/1/2005 12:00:00 AM 383b0cbf-d712-49d2-a05c-ed11f39732b0
12/1/2005 12:00:00 AM 383b0cbf-d712-49d2-a05c-ed11f39732b0
10/1/2005 12:00:00 AM ad34450a-9e74-4df6-915a-0009e9399ebc
11/1/2005 12:00:00 AM ad34450a-9e74-4df6-915a-0009e9399ebc
12/1/2007 12:00:00 AM 7ace9cdf-c66a-4ab4-b003-002686082199
11/1/2007 12:00:00 AM 7ace9cdf-c66a-4ab4-b003-002686082199

this needs to translate into the destination table as
10/1/2005 12:00:00 AM 1
11/1/2005 12:00:00 AM 2
12/1/2005 12:00:00 AM 3
10/1/2005 12:00:00 AM 1
11/1/2005 12:00:00 AM 2
12/1/2007 12:00:00 AM 1
11/1/2007 12:00:00 AM 2

I'm not exactly sure where to begin with this, i'm still relatively new to sql.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 18:41:59

[code]
SELECT datefield,ROW_NUMBER() OVER (PARTITION BY memid ORDER BY memid ) AS ID
FROM table
[/code]

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

Trilom
Starting Member

6 Posts

Posted - 2012-05-06 : 19:02:27

This is perfect, thanks again Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 19:18:58

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources