Update with inner Join - 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 2005 Forums

Transact-SQL (2005)

Update with inner Join

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-07 : 00:03:05

Update Tablea A Inner Join Tablea B On A.Tran_id = B.Tran_id
Set A.New_num = B.new_num + 1
Where A.Code Not in ( '07','08') and
A.Job_id = '12345'

Here I am trying to Update the New_num on table a.

The tablea has Job_id 12345 which holds 10 records.
The tran_id for every Job_id is Unique with Identity option.
Now we have input num_ip assigned to first record of New_num for the corresponding Job_id = 12345.
Later for every row there should be an Increment in New_num with following condition
If code is in (7,8) increment by 2 or increment by 1.
I found there is no code exists in 7,8 so I am trying with direct Update statement.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-07 : 00:23:10

Hoping I have got your question, Try this:
Update Tablea A
Set A.New_num=Case when A.Code in ( '07','08') then B.new_num + 2 else B.new_num + 1 end
from Tablea A Inner Join Tablea B On A.Tran_id = B.Tran_id
Where A.Job_id = '12345'

Also, make sure you in future you post table structures with sample data and expected output to allow others to help you in best possible way.

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 00:30:02

quote:
Originally posted by sunil

Hoping I have got your question, Try this:
Update Tablea A
Set A.New_num=Case when A.Code in ( '07','08') then B.new_num + 2 else B.new_num + 1 end
from Tablea A Inner Join Tablea B On A.Tran_id = B.Tran_id
Where A.Job_id = '12345'

Also, make sure you in future you post table structures with sample data and expected output to allow others to help you in best possible way.

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Change the table name near UPDATE. Just aliasname alone is needed. It will error if you include both

Update A
Set A.New_num=Case when A.Code in ( '07','08') then B.new_num + 2 else B.new_num + 1 end
from Tablea A 
Inner Join Tablea B 
On A.Tran_id = B.Tran_id
Where A.Job_id = '12345'

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-07 : 00:38:38

Table A

tran_id | Job_id | New_num | Sort_code
------ -------- -------- ---------
1.........12345.......0...........7
2.........12345.......0...........8
3.........12345.......0...........6
4.........12345.......0...........8
5.........12345.......0...........5
Now the Input for new_num in first rec is 100
and the output is

1........12345........100.......7
2........12345........102.......8
3........12345........103.......6
4........12345........105.......8
5........12345........106.......5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 01:07:17

Think this is what you are looking for:-


DECLARE @table table -- your table
(tran_id int,
Job_id int,
New_num int,
Sort_code int)
--insert the data
INSERT INTO @table VALUES(1,12345,0,7)
INSERT INTO @table VALUES(2,12345,0,8)
INSERT INTO @table VALUES(3,12345,0,6)
INSERT INTO @table VALUES(4,12345,0,8)
INSERT INTO @table VALUES(5,12345,0,5)

DECLARE @i int

SET @i=100--input value

UPDATE @table--updation
SET @i=New_num=CASE WHEN tran_id=1 THEN @i 
                    ELSE CASE WHEN Sort_code in (7,8) THEN @i+2 
                         ELSE @i+1
                         END
               END
WHERE Job_id=12345

--check the result
select * from @table


tran_id     Job_id      New_num     Sort_code
----------- ----------- ----------- -----------
1           12345       100         7
2           12345       102         8
3           12345       103         6
4           12345       105         8
5           12345       106         5

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-07 : 08:13:03

thnks a lot

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-07 : 08:32:43

I have this situtaion:

DECLARE @table table -- your table
(tran_id int,
Job_id int,
New_num int,
Sort_code int)
--insert the data
INSERT INTO @table VALUES(11,12345,0,7)
INSERT INTO @table VALUES(23,12345,0,8)
INSERT INTO @table VALUES(31,12345,0,6)
INSERT INTO @table VALUES(32,12345,0,8)
INSERT INTO @table VALUES(78,12345,0,5)
INSERT INTO @table VALUES(55,12346,0,7)
INSERT INTO @table VALUES(56,12346,0,8)
INSERT INTO @table VALUES(76,12346,0,6)
INSERT INTO @table VALUES(77,12346,0,8)
INSERT INTO @table VALUES(51,12346,0,5)
DECLARE @i int

SET @i=100--input value

;
with YAK(UIDS,tran_id,Job_id,New_num,Sort_code)
as
(
select Row_Number() over(partition by Job_id order by Job_id) UIDS,tran_id,Job_id,New_num,
Sort_code
from @table
)

UPDATE YAK
SET @i=New_num=CASE WHEN tran_id=1 THEN @i
ELSE CASE WHEN Sort_code in (7,8) THEN @i+2
ELSE @i+1
END
END
WHERE Job_id=12345

The above code works for Job_id=12345. How can I make it work for all Job_id.In sample code there are two distinct Job_id.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-07 : 08:36:32

I guess remove the where condition :)

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-07 : 08:39:30

By removing where condition , it continues incrementing for next Job_id too. Try runninng sample.What I am not able to get is to where to reset @i for different Job_id and update all Job_id in table with same logic.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-07 : 09:57:45

then in Where condition job_id = @Job_id
and declare the @job_id as input....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 11:03:23

quote:
Originally posted by sunil

I have this situtaion:

DECLARE @table table -- your table
(tran_id int,
Job_id int,
New_num int,
Sort_code int)
--insert the data
INSERT INTO @table VALUES(11,12345,0,7)
INSERT INTO @table VALUES(23,12345,0,8)
INSERT INTO @table VALUES(31,12345,0,6)
INSERT INTO @table VALUES(32,12345,0,8)
INSERT INTO @table VALUES(78,12345,0,5)
INSERT INTO @table VALUES(55,12346,0,7)
INSERT INTO @table VALUES(56,12346,0,8)
INSERT INTO @table VALUES(76,12346,0,6)
INSERT INTO @table VALUES(77,12346,0,8)
INSERT INTO @table VALUES(51,12346,0,5)
DECLARE @i int

SET @i=100--input value

;
with YAK(UIDS,tran_id,Job_id,New_num,Sort_code)
as
(
select Row_Number() over(partition by Job_id order by Job_id) UIDS,tran_id,Job_id,New_num,
Sort_code
from @table
)

UPDATE YAK
SET @i=New_num=CASE WHEN UIDS=1 THEN 100
WHEN tran_id=1 THEN @i
ELSE CASE WHEN Sort_code in (7,8) THEN @i+2
ELSE @i+1
END
END
WHERE Job_id=12345

The above code works for Job_id=12345. How can I make it work for all Job_id.In sample code there are two distinct Job_id.

Try like this & see

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-07 : 12:12:37

Thanks visakh. Just could not think of this.

DECLARE @table table -- your table
(tran_id int,
Job_id int,
New_num int,
Sort_code int)
--insert the data
INSERT INTO @table VALUES(11,12345,0,7)
INSERT INTO @table VALUES(23,12345,0,8)
INSERT INTO @table VALUES(31,12345,0,6)
INSERT INTO @table VALUES(32,12345,0,8)
INSERT INTO @table VALUES(78,12345,0,5)
INSERT INTO @table VALUES(55,12346,0,7)
INSERT INTO @table VALUES(56,12346,0,8)
INSERT INTO @table VALUES(76,12346,0,6)
INSERT INTO @table VALUES(77,12346,0,8)
INSERT INTO @table VALUES(51,12346,0,5)
DECLARE @i int

SET @i=100--input value

;
with YAK(UIDS,tran_id,Job_id,New_num,Sort_code)
as
(
select Row_Number() over(partition by Job_id order by Job_id) UIDS,tran_id,Job_id,New_num,
Sort_code
from @table
)

UPDATE YAK
SET @i=New_num=CASE WHEN UIDS=1 THEN 100
WHEN tran_id=1 THEN @i
ELSE CASE WHEN Sort_code in (7,8) THEN @i+2
ELSE @i+1
END
END

select * from @table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources