Better way to perform an update? (T-SQL)

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)

Better way to perform an update? (T-SQL)

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-06 : 06:46:43

Hi all, I hope you guys can help me with the following bit of T-SQL. I already have a solution but I really don't like it and I've been trying to find a simpler more elegant way of doing the same thing.

Firstly, let me present you with a brief explanation of what I am trying to do together with some sample data for you to play with and hopefully assist me in finding a better solution than the one I’ve come up with.

So.. here goes

I have two tables:

create table  #VehMake (VehMakeId int, VehMake varchar(100))

insert into #VehMake
select 222, 'FORD' union all
select 210, 'FORD (USA)' union all
select 223, 'FORD (AUS)' union all
select 269, 'HONDA' union all
select 253, 'NISSAN' union all
select 280, 'VOLKSWAGEN'

This contains various vehicle makes which I'm sure you'll recognise!

The second table contains vehicle histories:


create table #VehicleHistory (PersonId int, VehMakeVehModel varchar(200), VehMake varchar(100), VehModel varchar(100))

insert into #VehicleHistory (PersonId, VehMakeVehModel)
select 1, 'FORD (USA) MUSTANG' union all
select 2, 'HONDA CIVIC' union all
select 3, 'NISAAN ALMERA' union all
select 4, 'VOLKSWAGEN PASSAT'

As you can see, in the second table, the second column contains a string of the vehicle Make and Model in one string. What I need to do is to split the Make and Model in to separate columns with an update statement.

This seems easy enough with a simple LIKE comparison:


VehMakeVehModel like VehMake+' %'

....BUT if you notice, there are two records in the #VehMake table that are similar but not the same. These are the 'FORD (USA)' and 'FORD (AUS)'. The update statement would return two records from the #VehMake table when trying to match with the first record in my #VehicleHistory table.

As I said, I did come up with a solution but it seems over complicated and I have a feeling that there is a way of doing this with an update. Maybe use the LEN() function but I'm not sure.

Your help would be much appreciated.

BTW, once I've identified the correct Make, I can easily populate my model as all I have to do is use the replace function on VehMakeVehModel column and remove the matched make to get the full model name.

Hope that makes sense and thanks for any help in advance.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-06 : 06:58:44

Which column are you going to update and what is the expected result? Maybe a foreign key column VehMake in the #VehicleHistory table is desired to relate your tables.

Bjoern

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-06 : 07:01:07

Hi Bjoern,

Sorry I didn't make it clear but I need to update the VehMake and VehModel in the #VehicleHistory table. Anyway, I believe I have found a solution. This is it:


update a 
set VehMake = (select top 1 VehMake
				from #VehMake as b
				where ' ' + a.VehMakeVehModel + ' ' LIKE ' ' + b.VehMake + ' %' 
				order by len(b.VehMake) desc)
from #VehicleHistory as a

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-06 : 07:08:12

Hi again,

This is my complete solution:


update a 
set VehMake = (select top 1 VehMake
				from #VehMake as b
				where ' ' + a.VehMakeVehModel + ' ' LIKE ' ' + b.VehMake + ' %' 
				order by len(b.VehMake) desc),
	VehModel = (select top 1 replace(a.VehMakeVehModel, VehMake, '')
				from #VehMake as b
				where ' ' + a.VehMakeVehModel + ' ' LIKE ' ' + b.VehMake + ' %' 
				order by len(b.VehMake) desc)
from #VehicleHistory as a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 08:20:28

Try this too:-

UPDATE vh
SET vh.VehMake=v.Make,
    vh.VehModel=SUBSTRING(vh.VehMakeVehModel,PATINDEX(Make+'%',vh.VehMakeVehModel)+LEN(Make),LEN(vh.VehMakeVehModel))
FROM @VehicleHistory vh
CROSS APPLY (SELECT MAX(VehMake) AS Make FROM @VehMake 
WHERE PATINDEX(VehMake+'%',vh.VehMakeVehModel) >0)v

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-06 : 11:39:14

quote:

Originally posted by visakh16

Try this too:-

UPDATE vh
SET vh.VehMake=v.Make,
    vh.VehModel=SUBSTRING(vh.VehMakeVehModel,PATINDEX(Make+'%',vh.VehMakeVehModel)+LEN(Make),LEN(vh.VehMakeVehModel))
FROM @VehicleHistory vh
CROSS APPLY (SELECT MAX(VehMake) AS Make FROM @VehMake 
WHERE PATINDEX(VehMake+'%',vh.VehMakeVehModel) >0)v

Smart solution Vikash. I'm impressed. Can we have some explanation please? I've just been readin about the APPLy operator and I'm confused! Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 11:49:14

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

Try this too:-

UPDATE vh
SET vh.VehMake=v.Make,
vh.VehModel=SUBSTRING(vh.VehMakeVehModel,PATINDEX(Make+'%',vh.VehMakeVehModel)+LEN(Make),LEN(vh.VehMakeVehModel))
FROM @VehicleHistory vh
CROSS APPLY (SELECT MAX(VehMake) AS Make FROM @VehMake
WHERE PATINDEX(VehMake+'%',vh.VehMakeVehModel) >0)v

Smart solution Vikash. I'm impressed. Can we have some explanation please? I've just been readin about the APPLy operator and I'm confused! Thanks.

Sure. the CROSS APPLY will pass the value of VehMakeVehModel field from each row of @VehicleHistory to @VehMake which will look for presence of VehMake field of @VehMake table in @VehicleHistory and return the maximum field (Make) which matches this criteria to main query.
Then we use SUBSTRING to take the part of string that comes after the value in Make from VehMakeVehModel field. This will be the required VehModel value.
ex: for FORD (USA) MUSTANG the CROSS APPLY subquery returns FORD (USA). then we use PATINDEX to find start of FORD (USA) (position 1)and add to it length of string FORD (USA) (position 11)and take string starting from 11 th position which is MUSTANG.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-06 : 12:27:28

Thanks for this Vikash. What I don't really understand is the use of the APPLY operator. Everywhere I read, I hear that it is merely a inner join between a table and a table-valued function. In your example, you're using a MAX(). Forgive my stupidity but isn't MAX a scalar function? I am totally confused!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:42:22

quote:
Originally posted by Abu-Dina

Thanks for this Vikash. What I don't really understand is the use of the APPLY operator. Everywhere I read, I hear that it is merely a inner join between a table and a table-valued function. In your example, you're using a MAX(). Forgive my stupidity but isn't MAX a scalar function? I am totally confused!

APPLY operator enables us to pass fields from left side of query to subquery on right side to provide us with results based on the passed values from right side. Thus it can efficiently used in writing correlated subqueries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources