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

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 01:28:50

[code]
select * from emrappointmentdetails

select * from emrappointmentdetailshistory

select * from emrtpbilleditems

select * from emrappmtwstransactions

1.we need to join emrtpbilleditems and emrappmtwstransactions both tables like common column(MAPPING CASE) is appointment_id

flag_id,status,appointment_id are the columns from emrtpbilleditems table

appointment_id,tp_bill_number are the column from emrappmtwstransactions table

this join sholud be done based on flag_id='001' and '008'

2.update the values into emrappointmentdetails and emrappointmentdetailshistory table where tp_bill_number values from emrtpbilleditems
are to be updated in bill_number column in emrappointmentdetails and emrappointmentdetailshistory tables.

3.if flag_id='001' then is_tp_appointment column from emrappointmentdetails and emrappointmentdetailshistory tables
should updated as 0.
4.if flag_id='008' then is_tp_appointment column from emrappointmentdetails and emrappointmentdetailshistory tables
should updated as 1.

can you please give an update query where i need migration script for this.

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 02:44:18

Is the is question related to this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130580
or this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130389
or this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130601

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 02:49:34

yes peso help me out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 03:52:17

You haven't read and learned from the replies posted before?
If you want us to assist you, you have to post relevant information to us.

What is relevant information? Please read and understand why in this blog post
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

When you have read that blog post, please read this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

how to post back the relevant information.

When you do this, we are happy to help you.
If you don't, all we can do is guessing and that is a hyge waste of time, both for us and for you.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 04:28:53

ok please help me out in this case in need it very urgent

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 04:44:35

erm...i think you should give peso the sample data(requirement1), outputdate(requirement2) and what you wan us to make sample data to outputdata(requirement3)

Hope can help...but advise to wait pros with confirmation...

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 06:25:42

update emrappointmentdetailshistory_bak1 set is_tp_appointment=1 where bill_number in
(select tp_bill_number from (select a.appointment_id,a.flag_id,a.status,b.tp_bill_number,c.bill_number,c.is_tp_appointment
from emrappmtwstransactions_bak1 a,emrtpbilleditems_bak1 b,emrappointmentdetailshistory_bak1 c where a.appointment_id=b.appointment_id
and a.flag_id='008')d)

i have written like this but i want to update both my bill_number and is_tp_appointment column.how can i achieve that using using this update query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 06:49:22

Something like this?


UPDATE		c
SET		c.is_tp_appointment =	CASE a.flag_id
						WHEN '008' THEN 1
						WHEN '001' THEN 0
						ELSE is_tp_appointment	-- Do nothing
					END,
		c.bill_number = b.bill_number
FROM		emrappointmentdetailshistory_bak1 AS c
INNER JOIN	emrtpbilleditems_bak1 b AS b ON b.tp_bill_number = c.bill_number
INNER JOIN	emrappmtwstransactions_bak1 AS a ON a.appointment_id = b.appointment_id

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 06:57:48

peso,

UPDATE c
SET is_tp_appointment = CASE a.flag_id
WHEN '008' THEN 1
WHEN '001' THEN 0
ELSE is_tp_appointment -- Do nothing
END
FROM emrappointmentdetailshistory_bak1 AS c
INNER JOIN emrtpbilleditems_bak1 AS b ON b.tp_bill_number = c.bill_number
INNER JOIN emrappmtwstransactions_bak1 AS a ON a.appointment_id = b.appointment_id

iam getting exception like this,
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'IS_TP_APPOINTMENT'.

and i have to update bill_number column too for emrappointmentdetails_bak1 using tp_bill_number from emrtpbilleditems_bak1 table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:02:00

I think you get the idea behind the update suggested to you, for you to complete this assignment.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 07:05:14

no yours query i have written like this,

UPDATE c
SET c.bill_number=b.tp_bill_number,c.is_tp_appointment = CASE a.flag_id
WHEN '008' THEN 1
WHEN '001' THEN 0
ELSE is_tp_appointment -- Do nothing
END
FROM emrappointmentdetailshistory_bak1 AS c
INNER JOIN emrtpbilleditems_bak1 AS b ON b.tp_bill_number = c.bill_number
INNER JOIN emrappmtwstransactions_bak1 AS a ON a.appointment_id = b.appointment_id

getting exception as

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'IS_TP_APPOINTMENT'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:06:17

And since the BILL_NUMBER is the joining column, what do you want to update it with?
See my edited response above. It is still guessing BECAUSE YOU HAVEN'T BOTHERED TO READ THE LINKS I POSTED.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 07:08:09

now its working,

UPDATE c
SET
c.bill_number=b.tp_bill_number,
c.is_tp_appointment = CASE a.flag_id
WHEN '008' THEN 1
WHEN '001' THEN 0
ELSE c.is_tp_appointment -- Do nothing
END
FROM emrappointmentdetailshistory_bak1 AS c
INNER JOIN emrtpbilleditems_bak1 AS b ON b.tp_bill_number = c.bill_number
INNER JOIN emrappmtwstransactions_bak1 AS a ON a.appointment_id = b.appointment_id

got 6 rows effected

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:09:42

Why are you updating "c.bill_number=b.tp_bill_number," ???
The query is joining on that condition so "c.bill_number" already equals "b.tp_bill_number"...

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-06 : 07:14:53

i have to update bill_number too for emrappointmentdetailshistory_bak1 table.will it won't work

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-10 : 14:19:40

hire a DBA?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources