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

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-05 : 09:23:34

The sql below creates the tables and inserts values in question for you.

Question:
For every record in tblTableMain
get the value in each field and check if there is a criteria for that field in tblCriteria, if so then update tblTableMain.TypeNo according to tblCriteria.typeNo

I think the sql below makes it clear what I am after. Notice what the update does (It updates TypeNo field based on tblCriteria entries).

Thanks

create table #tblTableMain (TableMainID int identity(1, 1) , Field1 varchar(20),
Field2 varchar(20), Field3 varchar(20),
Field4 varchar(20), TypeNo tinyint)
insert into #tblTableMain(Field1, field2, field3, field4)
select 'uk', 'field2 value', '10', 'credit'
insert into #tblTableMain(Field1, field2, field3, field4)
select 'field11 value', 'field22 value', 'field33 value', 'field44 value'
insert into #tblTableMain(Field1, field2, field3, field4)
select 'us', '12', 'field333 value', 'debit'

select * from #tblTableMain
drop table #tblTableMain
GO

create table #tblCriteria(CriteriaID int identity(1, 1), tblTableMainFields varchar(20), operator varchar(20), value varchar(20), TypeNo tinyint)
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field1', '=', 'uk', 3
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field3', '<', '15', 3
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field4', '=', 'credit', 3
GO
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field1', '=', 'us', 2
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field2', '>', '5', 2
insert into #tblCriteria(tblTableMainFields , operator , value , TypeNo)
select 'Field4', '=', 'debit', 2

select * from #tblCriteria
drop table #tblCriteria
GO
--Result should show...
create table #tblTableMain (TableMainID int identity(1, 1) , Field1 varchar(20),
Field2 varchar(20), Field3 varchar(20),
Field4 varchar(20), TypeNo tinyint)
insert into #tblTableMain(Field1, field2, field3, field4, TypeNo)
select 'uk', 'field2value', '10', 'credit', 3
insert into #tblTableMain(Field1, field2, field3, field4, TypeNo)
select 'field11 value', 'field22 value', 'field33 value', 'field44 value', null
insert into #tblTableMain(Field1, field2, field3, field4, TypeNo)
select 'us', '12', 'field333 value', 'debit', 2

select * from #tblTableMain
drop table #tblTableMain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-05 : 10:41:30

This question is also asked over at MSDN forums at Microsoft.
You decide of you want to spend more time than already spent there.

N 56°04'39.26"
E 12°55'05.63"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-05 : 11:00:28

Hi,
Is it not a good idea to find out what other people think?
Thanks for your time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-05 : 13:26:16

You're welcome. Now I know whom need my time better.
I myself is not enjoying putting time and effort into something someone else may already have solved over at the other forum.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources