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

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 06:22:33

i have written a function
now i want to update a table column through afunction

my query is like this.


update emrappointmentdetailshistory_bak1  set a.bill_number=(select s.tp_bill_number from 
(
SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f WHERE VAL='806845'
))
go

exception:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 06:41:12

) as d )
go

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 06:45:25

Or this rewrite

UPDATE		x
SET		x.bill_number = y.tp_bill_number
FROM		emrappointmentdetailshistory_bak1 AS x
INNER JOIN	emrtpbilleditems_bak1 AS y ON ',' + y.appointment_ids + ',' LIKE '%,806845,%'

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 06:46:57

update emrappointmentdetailshistory_bak1 set bill_number=(select tp_bill_number from
(
SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f
)as d)
go

exception;

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 06:56:17

can you please send me the corrected ones peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 06:58:07

update emrappointmentdetailshistory_bak1 set bill_number = (select top 1 s.tp_bill_number from
(
SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f WHERE VAL='806845'
) as d)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 06:58:23

Or try my suggestion posted 08/04/2009 : 06:45:25

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 06:59:55

peso here where condition is not required.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 07:02:26

that one is giving message like
UPDATE x
SET x.bill_number = y.tp_bill_number
FROM emrappointmentdetailshistory_bak1 AS x
INNER JOIN emrtpbilleditems_bak1 AS y ON ',' + y.appointment_ids + ','

Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition is expected, near ','.

here i have removed like condition because i dont know particluar value

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 07:05:30

update emrappointmentdetailshistory_bak1 set bill_number = (select top 1 tp_bill_number from
(
SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f
) as d)

using this query my bill_number column is updating only single value and how can i update all with eqivalent values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:05:33

You are using a variable? Why do you post a fixed hardwired value when you are using a variable?

UPDATE		x
SET		x.bill_number = y.tp_bill_number
FROM		emrappointmentdetailshistory_bak1 AS x
INNER JOIN	emrtpbilleditems_bak1 AS y ON ',' + y.appointment_ids + ',' LIKE '%,' + @ParamHere + ',%'

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 07:08:16

i want all in a generalised way not through passing any parameter and i want it through a function peso

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 07:22:35

can you give me in a generalised query to update all the values please.all the time i can't pass values into that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:19:50

See this blog post, http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx .
Read it and understand it.
If you are not sure why you should read it, your answer is here, http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx .

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-04 : 09:59:28

Peter, it is easier if you just add those to your signature, like i have

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources