TSQL 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 2012 Forums

Transact-SQL (2012)

TSQL query

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-18 : 17:03:39

Can someone please tell me how to write a update query to update the #tmp1.StatusID column only if #tmp2.StatusID is 6 or 2 where #tmp1.ID = tmp2.ID1 (that's the relationship betn two tables)

For the below example: The #tmp1.StatusID should update where ID = 2. Because #tmp2.StatusID is 2 and 6 for ID1 = 2.

create table #tmp1 (ID INT, StatusID INT)
create table #tmp2 (ID INT, ID1 INT, StatusID INT)
insert into #tmp1 values (1, 1)
insert into #tmp1 values (2, 1)
insert into #tmp1 values (3, 1)

insert into #tmp2 values (1, 1, 1)
insert into #tmp2 values (2, 1, 2)
insert into #tmp2 values (3, 1, 6)
insert into #tmp2 values (4, 1, 2)

insert into #tmp2 values (5, 2, 2)
insert into #tmp2 values (6, 2, 2)
insert into #tmp2 values (7, 2, 2)
insert into #tmp2 values (8, 2, 6)

insert into #tmp2 values (9, 3, 1)
insert into #tmp2 values (10, 3, 5)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 17:19:04

How about this?



set nocount on

create table #tmp1 (ID INT, StatusID INT)
create table #tmp2 (ID INT, ID1 INT, StatusID INT)
insert into #tmp1 values (1, 1)
insert into #tmp1 values (2, 1)
insert into #tmp1 values (3, 1)

insert into #tmp2 values (1, 
1, 1)
insert into #tmp2 values (2, 1, 2)
insert into #tmp2 values (3, 1, 6)
insert into #tmp2 values (4, 1, 2)
insert into #tmp2 values (5, 2, 2)
insert into #tmp2 values (6, 2, 2)
insert into #tmp2 values (7, 2, 2)
insert into #tmp2 values (8, 2, 6)

insert into #tmp2 values (9, 3, 1)
insert into #tmp2 values (10, 3, 5)

select * from #tmp1

update #tmp1
set StatusID = 2
from #tmp1
join 
(
	select distinct ID1 from #tmp2 where StatusID = 2 or StatusID = 6
	except
	select distinct ID1 from #tmp2 where StatusID <> 2 and StatusID <> 6
) tmp2
on #tmp1.ID = tmp2.ID1

select * from #tmp1

drop table #tmp1, #tmp2

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-19 : 11:20:29

Thank you Tara. That worked great!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-19 : 12:07:53

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-25 : 12:38:34

One addtional condition client added is if all the records in #tmp2 are 6 that means update the #tmp1 with 6 not 2 because the transaction completed but code 6 stands for invalid. So if all the line items are invalid, the header should be updated with invalid. If partial line items are 6 but rest of them are 2 (complete) udpate the #tmp1 with 2 which currently is working.

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 12:46:26

I'll need to see sample data and expected output. You can add/subtract the sample data to the inserts in my test above and then show me expected output given that set of sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-25 : 13:12:52

Here is the sample data and expected results:

create table #tmp1 (ID INT, StatusID INT)
create table #tmp2 (ID INT, ID1 INT, StatusID INT)
insert into #tmp1 values (1, 1)
insert into #tmp1 values (2, 1)
insert into #tmp1 values (3, 5)
insert into #tmp1 values (4, 1)
insert into #tmp1 values (5, 1)

insert into #tmp2 values (1, 1, 1)
insert into #tmp2 values (2, 1, 2)
insert into #tmp2 values (3, 1, 6)
insert into #tmp2 values (4, 1, 2)

insert into #tmp2 values (5, 2, 2)
insert into #tmp2 values (6, 2, 2)
insert into #tmp2 values (7, 2, 2)
insert into #tmp2 values (8, 2, 6)

insert into #tmp2 values (9, 3, 1)
insert into #tmp2 values (10, 3, 5)
insert into #tmp2 values (11, 3, 5)

insert into #tmp2 values (12, 4, 6)
insert into #tmp2 values (13, 4, 6)

insert into #tmp2 values (14, 5, 2)
insert into #tmp2 values (15, 5, 2)

StatusID in #tmp1 should be -
StatusID = 5 for ID = 1
StatusID = 2 for ID = 2
For ID = 3, should not update to anything since all the line items in #tmp2.ID1 = 3 do not 2 or 6
StatusID = 6 for ID = 4, because all the line items in #tmp2.ID1 = 4 are 6

#tmp1 should be
1 5
2 2
3 5
4 6
5 2

If all the line items are 2 - update header with 2
If all the line items are 2 and 6 - update header with 2
If any of the line items have 1 or 5 - update header with 5
If all the line items have 6 - update header with 6

1 - new
2 - complete
5 - incomplete
6 - invalid

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-25 : 14:57:36

Hello Tara, I have provided the new sample data with the results. Can you please look into it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 16:24:17

I did look at it, but I couldn't follow it and got busy at work. Hopefully someone else in the forums can take a look.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-02-25 : 19:01:18

I can do this with 4 different updates however I wanted to do it in one/two. If you need additional sample data please let me know.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 05:00:14

[code]-- SwePeso
UPDATE t1
SET t1.StatusID = ISNULL(ABS(t2.Yak), 1)
FROM #tmp1 AS t1
LEFT JOIN (
SELECT ID1,
MIN(CASE WHEN StatusID IN (2, 6) THEN StatusID ELSE -5 END) AS Yak
FROM #tmp2
GROUP BY ID1
) AS t2 ON t2.ID1 = t1.ID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources