HELP Updating Column - 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 2000 Forums

Transact-SQL (2000)

HELP Updating Column

Author

Topic

BDesigns
Starting Member

12 Posts

Posted - 2006-11-15 : 11:44:42

How can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated.

Here is my table:

[CODE]create table #add_diags(
add_diag_1 varchar(10) null,
add_diag_2 varchar(10) null,
add_diag_3 varchar(10) null,
add_diag_4 varchar(10) null,
add_diag_5 varchar(10) null,
add_diag_6 varchar(10) null,
add_diag_7 varchar(10) null,
add_diag_8 varchar(10) null
)[/CODE]

In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on.

Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved):

[CODE]update ad
set add_diag_1=dsm_code
from #add_diags ad
join Doc_Entity de
on ad.patient_id=de.patient_id
and ad.episode_id=de.episode_id
and doc_code = 'DCDIAG'
join Patient_Assignment pa
on de.patient_assignment_id = pa.patient_assignment_id
and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
left outer join Doc_Diag_Axis_I_III dx1
on de.doc_session_no=dx1.doc_session_no
and de.current_version_no=dx1.version_no
where dx1.sequence_no=2
and de.is_locked = 'Y'
and dx1.rule_out = 'N'
and is_billable = 'Y'
and dx1.axis_type IN ('1','2')
and de.status in ('CO' , 'SA') [/CODE]

Any ideas? Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-15 : 12:13:58

1. You mean the #add_diags table will contains only 1 record ?

2. How did the record get inserted into #add_diags table ?

BDesigns
Starting Member

12 Posts

Posted - 2006-11-15 : 12:41:52

Correct, the #add_diags table will only have one row, so if column 'add_diag_1' has a value in it, based off my update criteria, put the next value in the 'add_diag_2' column. The biggest issue I face is, I am pulling values for all of these columns, but in my WHERE statement I have:

and dx1.axis_type IN ('1', '2')

Which means I am only populating this row when this 'axis_type' is met. I then again repeat the same update with a different 'axis_type' that is '3' and this is where I can't overwrite what was pulled when the 'axis_type' equaled 1 or 2, I must put the values derived from axis_type = 3 into the next available column:


update  ad
	set  add_diag_1=dsm_code
	from #add_diags  ad
	join Doc_Entity de 
		on ad.patient_id=de.patient_id
		and ad.episode_id=de.episode_id
		and doc_code = 'DCDIAG' 
	join Patient_Assignment pa
		on de.patient_assignment_id = pa.patient_assignment_id
		and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
	left outer join Doc_Diag_Axis_I_III dx1 
		on de.doc_session_no=dx1.doc_session_no
		and de.current_version_no=dx1.version_no
	where  dx1.sequence_no=2
	   and de.is_locked = 'Y'
	   and dx1.rule_out = 'N' 
	   and is_billable = 'Y'
	   and dx1.axis_type IN ('3')
	   and de.status in ('CO' , 'SA')

So what I need to do is, if values from the first update resulted in columns 'add_diag_1', and 'add_diag_2' to become populated, I must CHECK to see if they are populated and then take the results from my second update that deals with dx1.axis_type IN ('3') and populate into the next available column, ie. 'add_diag_3' leaving what was in the first two.

BDesigns
Starting Member

12 Posts

Posted - 2006-11-16 : 10:13:35

Any idea how this can be done in a loop for SQL? This is a part of a rather lengthy stored procedure that is going to be updating a PowerBuilder data window, which unfortunately cannot be modified so the work is done in the stored procedure itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources