Update with sub query - 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 2000 Forums

Transact-SQL (2000)

Update with sub query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-21 : 10:54:28

Sorry, here is what i like to acheive.

I've a table of data which hold codes.

If the code = 26 i'd like to set a Declined_ind equal to 1. I want to use a group by on the unique_id as there might be some duplicates.

Here is my result set which gives me back what i like without the group by i get more records as you'd expect, the Table am updating holds all the columns source_system, log_number

Update Table
Set Declined_ind = '1'
where .....
(
SELECT source_system, log_number AS unique_id
FROM tbl_raw_icss_call_actions
WHERE dch_code = N'26')
GROUP BY source_system, log_number
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:55:57

We are lost too!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 10:59:37

[code]Update t1
Set Ind = '1'
From Table t1 Join
(
SELECT source_system, log_number AS unique_id
FROM tbl_raw_call_actions
WHERE (dch_code = '6')
GROUP BY source_system, log_number
) t2 on t1.source_system = t2.source_system and t1.unique_id = t2.unique_id[/code]

I can't strech my imagination beyond that !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-21 : 11:54:26

I've tried this but am not getting the same count result set as just the query without the update..

Update tbl_Hot_Transferred_Staging
set Declined_ind = '1'
From tbl_Hot_Transferred_Staging join
( SELECT source_system,log_number
FROM tbl_raw_icss_call_actions t2
WHERE dch_code = '26'
GROUP BY t2.source_system,t2.log_number
) t2 on tbl_Hot_Transferred_Staging.source_system = t2.source_system and tbl_Hot_Transferred_Staging.log_number = t2.log_number

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 13:15:35

Give us some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-22 : 09:06:13

ok here is my data see code below.

The expected out put should be this. Based on the data in both tables

Source system log number dch_code Declined_ind
sys_1 100370613 26 1
sys_1 100370614 1 0
sys_1 100370615 2 0

If i have the dch_code set the Declined_ind = 1 also group all the log_number together in this case long number 100370613 is in the table 2 times but in the result set its only showen once..

drop table #tbl_Hot_Transferred_Staging
drop table #tbl_raw_icss_call_actions

create table #tbl_Hot_Transferred_Staging
(
source_system varchar(10),
log_number nvarchar(10)

)
insert into #tbl_Hot_Transferred_Staging(source_system,log_number)
values ('sys_1','100370613')
insert into #tbl_Hot_Transferred_Staging(source_system,log_number)
values ('sys_1','100370614')

create table #tbl_raw_icss_call_actions
(
source_system varchar(10),
log_number nvarchar(10),
dch_code int

)
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370613','26')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370614','01')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370615','02')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370613','26')

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-22 : 15:34:29

rookie_sql,

Ummm... Based on the DDL you posted, there is no "Declined_Ind" in either table. How are we supposed to show you how to update a non-existant column?

Please post the correct DDL for the tables. Then someone can help.

Ken

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-01-04 : 10:13:05

-- I've posted the DDL of the 2 tables i like to apply an update on the column Declined_Ind
-- setting it to 1 or 0 depending on the dch_code, from both tables i need to join both tables together
-- and group by the source_system and log_number so i don't show any duplicates

-- The expected out put should be this, based on the data in both tables

--Source system log number dch_code Declined_ind
-- sys_1 100370613 26 1
-- sys_1 100370614 1 0
-- sys_1 100370615 2 0

-- If i have the dch_code set to 26 the Declined_ind should equal to 1
-- also grouping all together in this case the log number 100370613
-- is in the table 2 times but in the result set, its only showen once.

drop table #tbl_Hot_Transferred_Staging
drop table #tbl_raw_icss_call_actions

create table #tbl_Hot_Transferred_Staging
(
source_system varchar(10),
log_number nvarchar(10),
Declined_Ind int

)
insert into #tbl_Hot_Transferred_Staging(source_system,log_number,Declined_Ind)
values ('sys_1','100370613',NULL)
insert into #tbl_Hot_Transferred_Staging(source_system,log_number,Declined_Ind)
values ('sys_1','100370614',NULL)

create table #tbl_raw_icss_call_actions
(
source_system varchar(10),
log_number nvarchar(10),
dch_code int

)
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370613','26')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370614','01')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370615','02')
insert into #tbl_raw_icss_call_actions(source_system,log_number,dch_code)
values ('sys_1','100370613','26')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources