RE: SQL question

Following assumes:
1) you want to update POST_PROCEDURE rows whose PROCEDURE_ID = 131200 and
have an invalid date (i.e. less than 11/01/1999)
2) there is a 1-to-1 correspondence between two tables on CASE_NO
3) POST_ANESTHESIA_TYPE table's START_DATE & START_TIME do not have invalid
values. If they may have invalid values you may need a CASE clause in the
projection area of the 1st subselect to force a valid date. Or enhance the
WHERE clause further to weed these rows out as well).

Chances are some of my assumptions are not correct so use caution, but
something like this should be a good starting point:

UPDATE POST_PROCEDURE p
SET (p.START_DATE, p.START_TIME) =
(SELECT a.START_DATE, a.START_TIME
FROM POST_ANESTHESIA_TYPE a
WHERE p.CASE_NO = a.CASE_NO)
WHERE p.PROCEDURE_ID = '131200' AND
p.START_DATE < '11/01/1999' AND EXISTS
(SELECT 1
FROM POST_ANESTHESIA_TYPE a
WHERE p.CASE_NO = a.CASE_NO)

Post back if you have problems with it.

Elvis

RPG & DB2 Summit | Minneapolis | October 1-4
Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400

-----Original Message-----
Subject: SQL question

I know how to do simple stuff in MS-SQL. But, I need to make a database
change
that I am not confident about.

The database is on an MS-SQL server.

I realize this is not a Midrange-specific question, and I apologize for
that.

Basically, I need to populate two columns in one table with corresponding
values
from another table. But only under specific circumstances.

I do not want to change the first table's values if they are already
populated.
In the case of this database, there can be no valid dates prior to
11/01/1999.
The database design is not mine. The vendor is using two date-time columns,
one stores actual date and a time of zero. The second stores the default
date
1/1/1800 and the actual time.

What I have come up with, I present below. It is incomplete. I do not know
how
to specify the second table properly. The two tables are POST_PROCEDURE
which I
want to selectively update, and POST_ANESTHESIA_TYPE, which has the data I
need.

The two tables have a common column, CASE_NO.


Any help is appreciated.

Here is what I have come up with:

UPDATE POST_PROCEDURE p
SET p.START_DATE = , p.START_TIME =
WHERE p.CASE_NO = a.CASE_NO
AND p.PROCEDURE_ID = '131200'
AND p.START_DATE < '11/01/1999'

John McKee