query help - 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

query help

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-18 : 07:18:11

dear experts,

I've one table.
in that table, column2 should be updated with one condition
the condition is

update table019 set columnb02=(select column04 from table021 where column01='0463c12b-1a06-44a3-a709-a1b7b8cd2914' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='0463c12b-1a06-44a3-a709-a1b7b8cd2914'

here column03 is common for all the script

how can i update this table with a function?
column01 has around 279 values

this means, this inner query is giving 279 values.

please help me to write a function

I've written like this....
but unable to get the result

create function fnuom(@uomid varcahr(50))
returns varchar(50)
as
begin
declare @itemid as varchar(50)
declare @conv as varchar(50)
select @itemid=column01 from table019 where column03= @uomid
select @conv=column04 from table021 where column01= @itemid and column03= @uomid
return @conv
end
go

thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-18 : 11:35:02

Why you need function for this? This is simple join:

Update t19
Set columnb02 = t21.column04
From table019 t19 Join table021 t21 on t19.column01 = t21.column01
Where t21.column01 = '0463c12b-1a06-44a3-a709-a1b7b8cd2914' and
t21.column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-20 : 02:47:39

Dear Harsh,
here in the query,
the value '0463c12b-1a06-44a3-a709-a1b7b8cd2914' will be differed for around 279 times. everytime one new value will com to this place.
that's why I'm about function

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-23 : 01:27:51

here in the query,
the value '0463c12b-1a06-44a3-a709-a1b7b8cd2914' will be differed for around 279 times. everytime one new value will come to this place.that was a newid....
that's why I'm asking about function

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-23 : 01:43:14

the example script written by me is like this...

update table019 set columnb2=(select column04 from table21 where column01='009c0a89-a1a8-4d63-8c4e-d80365971d75' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='009c0a89-a1a8-4d63-8c4e-d80365971d75'
update table019 set columnb2=(select column04 from table21 where column01='01984009-6776-404e-bb81-fd7b3b74eee4' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='01984009-6776-404e-bb81-fd7b3b74eee4'
update table019 set columnb2=(select column04 from table21 where column01='03a05e50-4c9a-4f98-8141-9cd252fd7b09' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='03a05e50-4c9a-4f98-8141-9cd252fd7b09'
update table019 set columnb2=(select column04 from table21 where column01='042028f5-3242-4079-b0b5-d3f7d68b18a8' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='042028f5-3242-4079-b0b5-d3f7d68b18a8'
update table019 set columnb2=(select column04 from table21 where column01='0463c12b-1a06-44a3-a709-a1b7b8cd2914' and column03='1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') where column01='0463c12b-1a06-44a3-a709-a1b7b8cd2914'
like this some hundreds of lines should be updated.

now my problem is i dont want to write a script like this.
by just running one function, that should be automatically updated.

please help me in this regard.

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 03:05:19

Something like this

UPDATE		t19
SET		t19.Column02 = d.Column04
FROM		Table019 AS t19
INNER JOIN	dbo.GetItemID('1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') AS d ON d.Column03 = t19.Column03

Peter Larsson
Helsingborg, Sweden

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-24 : 03:12:38

thank you peso

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 03:22:06

Another Maya_Zakry delivery

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 04:53:53

quote:
Originally posted by Peso

Another Maya_Zakry delivery

Peter Larsson
Helsingborg, Sweden

What do you mean ? How is this relate to Maya_Zakry ?

KH
[spoiler]Time is always against us[/spoiler]

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-07-24 : 06:47:04

Dear Experts,
I've completed my task with all your help especially KH and Peso....very much thankful to you all.

now is it possible to make all this as one proceudre or one function? This is my idea to spiltting like this . actually i've to update the table19.columnb2. based on all these. please give me the way to make all the two functions and the query to run at a time.

CREATE function getuomid(@uomcode varchar(50))
returns varchar(50)
as begin
declare @uom_id varchar(50)
select @uom_id= uom_id from vuom where uom_code=@uomcode
return @uom_id
end

CREATE function getitemid(@uomid varchar(50))
returns table
as
return (select * from table21 where column03=(select dbo.getuomid('no of leaves')))

UPDATE t19
SET t19.Columnb2 = d.Column04
FROM Table19 AS t19
INNER JOIN dbo.GetItemID('1ca7a0fa-c507-4d4d-94a1-13eb9ec3d6d2') AS d ON d.Column01 = t19.Column01

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:39:40

Your input parameter to GetItemID is not even used!!!

CREATE PROCEDURE dbo.spMyProc
AS

UPDATE		t19
SET		t19.Columnb2 = d.Column04
FROM		Table19 AS t19
INNER JOIN	Table21 AS t21 ON t21.Column01 = t19.Column01
INNER JOIN	vuom ON vuom.uom_id = t21.Column03
WHERE		vuom.uom_code = 'no of leaves'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources