Updating table with values from other 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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating table with values from other table

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-01 : 10:24:34

I have a table as below

Col1 usr_GroupUsr
-----------------------
ser1 individual1
ser2 group1
ser2 individual2
ser3 individual3
ser4 group2
ser4 group3

TAB2

group usr
------------------------
group1 individual1
group1 individual2
group1 individual3
group2 abc1
group2 xyz1
group2 abc2
group3 abc1
group3 abc2

I want to o/p of the table 1 where ever group1..n is there
should be replace with all users for that particular group from table 2 other colum value would repeat like ser col value can repeate.

can any one help me in updating this.

regards,
aakcse

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 10:50:56

[code]
-- Fake tables
declare @yak table( Col1 char(4), usr_GroupUsr varchar(15))

declare @llama table ([group] varchar(15), usr varchar(15))

-- insert our data
insert into @yak (Col1,usr_GroupUsr)
SELECT 'ser1', 'individual1'
UNION ALL SELECT 'ser2', 'group1'
UNION ALL SELECT 'ser2', 'individual2'
UNION ALL SELECT 'ser3', 'individual3'
UNION ALL SELECT 'ser4', 'group2'
UNION ALL SELECT 'ser4', 'group3'

insert into @llama([group],usr)

SELECT 'group1', 'individual1'
UNION ALL SELECT 'group1', 'individual2'
UNION ALL SELECT 'group1', 'individual3'
UNION ALL SELECT 'group2', 'abc1'
UNION ALL SELECT 'group2', 'xyz1'
UNION ALL SELECT 'group2', 'abc2'
UNION ALL SELECT 'group3', 'abc1'
UNION ALL SELECT 'group3', 'abc2'

-- do the work
select
y.Col1,CASE WHEN l.[group] IS NULL THEN y.usr_GroupUsr ELSE l.usr END as Usr
FROM
@yak y
LEFT JOIN
@llama l
ON
y.usr_GroupUsr = l.[group]
[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-01 : 10:58:08

Thanks,

however the group are more in nuber say there are around 50 groups.

Thanks again

Regards,
aak

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-01 : 11:43:40

Can you give the expected output based on your sample data. This will make things clear.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 11:50:32

Try it and see what happens. Otherwise, follow the first link in my signature and restate your question.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-01 : 13:27:43

My sincere apology if, the question was not clear.

Expected out put would be like

Table1

Col1 usr_groupUsr
---------------------
ser1 individual1
ser2 individual1
ser2 individual2
ser2 individual3
ser3 individual3
ser4 abc1
ser4 xyz1
ser4 abc2
ser4 abc1
ser4 abc2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:37:30

[code]
SELECT Col1,Usr
FROM
(
SELECT t1.Col1, t1.usr_GroupUsr AS Usr
FROM table1 t1
LEFT JOIN table2 t2
ON t2.group=t1.usr_GroupUsr
WHERE t2.group IS NULL
UNION
SELECT t1.Col1,t2.usr
FROM table1 t1
JOIN table2 t2
ON t2.group=t1.usr_GroupUsr
)t
ORDER BY Col1,Usr
[/code]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 14:09:34

Why would ser4 have a repeating abc2, but ser2 not have a repeating individual2?
Your expected output does not match your given data and criteria. The code i posted gives what you asked for.


Col1	Usr
ser1	individual1
ser2	individual1
ser2	individual2
ser2	individual3
ser2	individual2
ser3	individual3
ser4	abc1
ser4	xyz1
ser4	abc2
ser4	abc1
ser4	abc2

You should get ser2 individual2 from group1, and also from the individual record containing individual2.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-01 : 14:11:25

Thank you all.

quote:

Originally posted by visakh16


SELECT Col1,Usr
FROM
(
SELECT t1.Col1, t1.usr_GroupUsr  AS Usr
FROM table1 t1
LEFT JOIN table2 t2
ON t2.group=t1.usr_GroupUsr
WHERE t2.group IS NULL
UNION 
SELECT t1.Col1,t2.usr
FROM table1 t1
JOIN table2 t2
ON t2.group=t1.usr_GroupUsr
)t
ORDER BY Col1,Usr

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 14:16:00

visakh's code does not give you what you asked for. It eliminates duplicates. you asked for them to be there. Which is it?

Otherwise, slap a DISTINCT clause on my code and you get what you want also.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-02 : 00:30:41

Point noted Don.

quote:
Originally posted by DonAtWork

visakh's code does not give you what you asked for. It eliminates duplicates. you asked for them to be there. Which is it?

Otherwise, slap a DISTINCT clause on my code and you get what you want also.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-02 : 03:36:31

The below query I have tested for couple of recrods it looks to be fine.

Don is this correct?

SELECT A.col1, COALESCE(B.usr,A.usr_groupUsr) AS usr
FROM Tab1 A LEFT JOIN Tab2 B
ON A.usr_groupUsr = B.groups

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-04-02 : 05:18:49

select
a.col1,
CASE
WHEN a.usr_GroupUsr= b.groupTHEN b.usr
ELSE a.usr_GroupUsr
END Only_Users
from
tab1 a LEFT JOIN tab2 b
ON A.usr_GroupUsr = B.group

The above select query is also giving me the same results as the above is giving.
is this right.

Regards,
aak

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-02 : 10:36:58

If the query gives you what you want, then i would say it is right.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources