update table - 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

General SQL Server Forums

New to SQL Server Programming

update table

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2014-08-06 : 09:14:33

I have two table

Table 1

[ID] [Wt] [Range]

A01 33

Table 2

[ID] [Range1] [Range2]

A01 10-30 30-40

I want to update the table 1 range to

30-40 from Table 2 as weight 33

which is under range 2

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 09:31:03

try:

Update table1
Set a.[Range] = b.[Range2]
From table1 a
Inner Join table2 b
On a.[id] = b.[id]
Where a.[Wt] = 33

We are the creators of our own reality!

pnasz
Posting Yak Master

101 Posts

Posted - 2014-08-06 : 10:07:05

what if weight is 20 then it should put the value of range 1 in the range field of table 1 as now wt will be under the range 1 (10-30)

there will be many weight so it should select proper range

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 10:27:09

Update table1
Set a.[Range] = Case
When a.[Wt] = b.[Range1]
Then b.[Range1]
When a.[Wt] = b.[Range2]
Then b.[Range2]
Else 'No Range'
End
From Table1
Inner Join table2 b
On a.[id] = b.[id]

We are the creators of our own reality!

pnasz
Posting Yak Master

101 Posts

Posted - 2014-08-06 : 10:28:53

Table 1

[ID] [Wt] [Range]

A01 33

A01 20

A01 39

A01 25

Table 2

[ID] [Range1] [Range2]

A01 10-30 30-40

After update table whould be like this

Table 1

[ID] [Wt] [Range]

A01 33 30-40

A01 20 10-30

A01 39 30-40

A01 25 10-30

please help

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 11:04:07

Im afraid its your design here, you have unique id Im assuming is your primary key A01, you cant use the same unique id, autoincrement or if the A01 is a code of something you need to call it CodeID but then still have a unique primary key field such as table1id i.e(table definition) for the id name. You can then populate table1 based on changed from table2 where the id's meet so, A01, A02, A03...as primary or T1id, T2id...Another issue you have is your ranges, 10-30 and 30-40, this is not good for querying in the way you want using calculations as these are string types...you want to split you ranges to Range1 10, Range2 20, Range3 30, Range4 40, this will make the queries much more easier to calculate, otherwise you are reading strings. You can also use the between function to read between ranges from the Ranges...

Create table #table1
(
id varchar(5) not null primary key,
Wt varchar(10) null,
Range varchar(10) null
)
GO

Create table #table2
(
id varchar(5) not null primary key,
Range1 varchar(10) null,
Range2 varchar(10) null
)
GO

Insert into #table1 (id, Wt)
Values ('A01', 33),
('A02', 20),
('A03', 39),
('A04', 25)
GO

Insert Into #table2
Values ('A01', 10-30, 30-40)

select * from #table1
select * from #table2

Update #table1
Set [Range] = Case
When [Wt] in ([Range1])
Then [Range1]
When [Wt] In ([Range2])
Then [Range2]
Else 'No Range'
End
From #Table1
Inner Join #table2
On #table1.[id] = #table2.[id]

We are the creators of our own reality!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 11:18:57

This temp script may help you out:

Create table #table1
(
id varchar(5) not null primary key,
Wt varchar(10) null,
Range varchar(10) null
)
GO

Create table #table2
(
id varchar(5) not null primary key,
Range1 varchar(10) null,
Range2 varchar(10) null,
Range3 varchar(10) null,
Range4 varchar(10) null
)
GO

Insert into #table1 (id, Wt)
Values ('A01', 33),
('A02', 20),
('A03', 39),
('A04', 25)
GO

Insert Into #table2
Values
('A01', 10, 20, 30, 40),
('A02', 10, 20, 30, 40),
('A03', 10, 20, 30, 40),
('A04', 10, 20, 30, 40)

select * from #table1
select * from #table2

Update #table1
Set [Range] = Case
When [Wt] between ([Range1]) and ([Range3])
Then [Range1] + ' - ' + [Range3]
When [Wt] Between ([Range3]) and ([Range4])
Then [Range3] + ' - ' + [Range4]
Else '[No Range]'
End
From #Table1
Inner Join #table2
On #table1.[id] = #table2.[id]

select * from #table1

Output:

id Wt Range
A01 33 30 - 40
A02 20 10 - 30
A03 39 30 - 40
A04 25 10 - 30

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-06 : 11:51:14

I agree with sz1 that the design should be looked into.
However - assuming table2 ranges are ALWAYS "number dash number" without spaces or other chars, this might work for you:

update table1
   set table1.range=case
                when table1.wt>=cast(substring(table2.range1,1,charindex('-',table2.range1)-1) as int)
                 and table1.wt<=cast(substring(table2.range1,charindex('-',table2.range1)+1,99) as int)
                then table2.range1
                when table1.wt>=cast(substring(table2.range2,1,charindex('-',table2.range2)-1) as int)
                 and table1.wt<=cast(substring(table2.range2,charindex('-',table2.range2)+1,99) as int)
                then table2.range2
                else table1.range
             end
  from table2
 where table2.id=table1.id
   and ((table1.wt>=cast(substring(table2.range1,1,charindex('-',table2.range1)-1) as int)
   and   table1.wt<=cast(substring(table2.range1,charindex('-',table2.range1)+1,99) as int)
        )
    or  (table1.wt>=cast(substring(table2.range2,1,charindex('-',table2.range2)-1) as int)
   and   table1.wt<=cast(substring(table2.range2,charindex('-',table2.range2)+1,99) as int)
       ))

pnasz
Posting Yak Master

101 Posts

Posted - 2014-08-07 : 01:57:05

Thanx a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources