Update column with inforfrom another table

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

Update column with inforfrom another table

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-09-20 : 10:15:42

I have a table that I want to update the GROUP column with the information in another tables GROUP column based on the to tables matching SUB_ID fields.

Here is my schema:
2 tables - Subscription and navwarupdate
the 2 subscription fields are sub_id and group_id
the 2 navwarupdate fields are sub_id and group_id

I want to update the group_id field in SUBSCRIPTION table with the info in group_id in navwarupdate table.

Is this query correct?

UPDATE SUBSCRIPTION
SET group_id = ‘navwarupdate.group_id’
WHERE sub_id in (SELECT navwarupdate.sub_id FROM navwarupdate)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:19:13

UPDATE S
SET group_id = N.group_id
FROM SUBSCRIPTION S
INNER JOIN navwarupdate N
ON S.sub_id = N.sub_id

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:23:57

since you want to update the table Subscription with corresponding values of group_id for a sub_id in the navwarupdate table you need to relate the two using joins.

jatrix32
Starting Member

49 Posts

Posted - 2010-09-20 : 10:44:45

awesome, that worked!

Now for step 2:

Now that I have populated my group_id field in my SUBSCRIPTION table, I want to populate a third table (account_user_groups) based on the group_id of the SUBSCRIPTION table. Here is the tricky part, the group_id filed in the SUBSCRIPTION table is a comma delimited group numbers, for example: 3,28,114
When I move this data to the account_user_groups table, I want each group to be a separate record, for example:
record 1 group_id 3
record 2 group_id 28
record 3 group_id 114

Also sub_id is still used to link the 2 tables, so this will be an update.

Here is my schema:

Subscription table - group_id, sub_id
account_user_table - group_id, sub_id

This is tricky, I am not even sure what this query will look like.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:33:24

quote:
Originally posted by jatrix32

awesome, that worked!

Now for step 2:

I want to populate a third table (account_user_groups) based on the group_id of the SUBSCRIPTION table. Here is the tricky part, the group_id filed in the SUBSCRIPTION table is a comma delimited group numbers, for example: 3,28,114
When I move this data to the account_user_groups table, I want each group to be a separate record, for example:
record 1 group_id 3
record 2 group_id 28
record 3 group_id 114

Also sub_id is still used to link the 2 tables, so this will be an update.

Here is my schema:

Subscription table - group_id, sub_id
account_user_table - group_id, sub_id

This is tricky, I am not even sure what this query will look like.

you want to say that account_user_groups table is having repeated sub_id's. As far as I have understood your scenario, it is something like this:

you have Subscription Table with data like:

sub_id group_id
1 '3,5,7'
2 '9,4,3'

and account_user_groups table with data like

sub_id group_id
1 3
1 4
2 8
2 7

and after UPDATION it should be like

sub_id group_id
1 1
1 3
1 5
1 7
2 9
2 4
2 3

Is it so?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:36:28

If it is I will give you the query by tommorow.

jatrix32
Starting Member

49 Posts

Posted - 2010-09-20 : 11:52:01

That is exactly what I want to do. I am getting lost when it comes to parsing the group_ids into singular fields.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 03:52:16

quote:
Originally posted by jatrix32

That is exactly what I want to do. I am getting lost when it comes to parsing the group_ids into singular fields.

Then it will be a combination of INSERT & UPDATE. Will give you the query soon

Thanks
Rohit

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 04:30:47

hi,

Following is your query:



CREATE TABLE #tmp
(
id INT IDENTITY(1,1),
sub_id INT,
group_id VARCHAR(50)
)

INSERT INTO #tmp
SELECT * FROM navwarupdate

DECLARE @cnt INT
DECLARE @max_cnt INT

SET @cnt = 1
SELECT @max_cnt = COUNT(*) FROM #tmp

WHILE (@cnt <= @max_cnt)
BEGIN
     DECLARE @sub_id INT
     DECLARE @with_comma_group_id VARCHAR(50)
      
     SELECT @sub_id = sub_id , @with_comma_group_id = group_id FROM #tmp WHERE id= @cnt
      
     SELECT * INTO #row_wise_data FROM dbo.fn_SplitDelimitedString(',' , @with_comma_group_id) 
      
     DECLARE @inner_cnt INT
     DECLARE @inner_max_cnt INT
	  
     SET @inner_cnt = 1
     SELECT @inner_max_cnt = COUNT(*) FROM #row_wise_data
      
     WHILE (@inner_cnt <= @inner_max_cnt)
     BEGIN  
         DECLARE @group_id INT
         SET  @group_id = (SELECT group_id FROM #row_wise_data WHERE row_number = @inner_cnt)
			 
	IF NOT EXISTS(SELECT 1 FROM subscription WHERE sub_id = @sub_id AND group_id = @group_id) -- IF sub_id&group_id absent
         BEGIN
	      INSERT INTO subscription(sub_id,group_id)
	      SELECT @sub_id , @group_id
	 END
	 SET @inner_cnt = @inner_cnt + 1
      END
      SET @cnt = @cnt + 1
      DROP TABLE #row_wise_data
      
END

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 04:31:56

In order to execute the above query you will have to create the following fucntion

CREATE FUNCTION [dbo].[fn_SplitDelimitedString]
(@Delimiter char(1),
@InputList varchar(2000))
returns @SplittedTable table (row_number int, group_id varchar(100)) -- Table structure returned
AS

BEGIN

DECLARE @Item varchar(100),
@CriteriaCount int

SET @CriteriaCount = 1

WHILE CHARINDEX(@Delimiter,@InputList,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@InputList,1,CHARINDEX(@Delimiter,@InputList,0)-1))),
@InputList=RTRIM(LTRIM(SUBSTRING(@InputList,CHARINDEX(@Delimiter,@InputList,0)+1,LEN(@InputList))))

IF LEN(@Item) > 0
INSERT INTO @SplittedTable (row_number, group_id) VALUES (@CriteriaCount, @Item)

SET @CriteriaCount = @CriteriaCount + 1
END

IF LEN(@InputList) > 0
INSERT INTO @SplittedTable (row_number, group_id) VALUES (@CriteriaCount, @InputList) -- Put the last item IN

RETURN -- Returns the table.

END

jatrix32
Starting Member

49 Posts

Posted - 2010-09-21 : 11:02:01

excellent, thank you!!!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 02:17:06

quote:
Originally posted by jatrix32

excellent, thank you!!!

Always Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources