SQL Update Statement Based on some date Value '00000000'

Question

SQL Update Statement Based on some date Value '00000000'

Saintmount.Sql

Ten Centuries

Points: 1023
More actions
September 4, 2011 at 5:57 pm

#390337

Hi All,
I have requirement is like Do not update the cloumn, if the Date is NULL value or BLANK/EMPTY or has a '00000000' value. But I need to update if any of Date value is not '00000000'.
Please find Query for more Idea.
I have the below update statement.
UPDATE D
SET D.CHANGEDATE= COALESCE(A.date_exp1,A.date2,B.date3,C.date4)
FROM TBL A
JOIN CONDITONS FOR TABLES B,C,D
WHERE A.P_S IN ('c1','c2')
AND COALESCE(A.date_exp1,A.date2,B.date3,C.date4) IS NOT NULL
AND COALESCE(A.date_exp1,A.date2,B.date3,C.date4) <>''
AND COALESCE(A.date_exp1,A.date2,B.date3,C.date4) <> '00000000'
ANALYSIS:
SELECT A.date_exp1,A.date2,B.date3,C.date4,D.P_ID
FROM TBL A
JOIN CONDITONS FOR TABLES B,C,D
WHERE A.P_S IN ('c1','c2')
Result:
A.date_exp1||A.date2||B.date3|| C.date4||D.P_ID
00000000' 00000000' NULL 20190906 CO863
00000000' 00000000' NULL 20190906 CO863
00000000' 00000000' NULL 20190906 CO863
00000000' 00000000' NULL 20190906 CO863
00000000' 00000000' NULL 00000000' CO332
00000000' 00000000' NULL 00000000' CO332
Based on the above select statement C.date3 value need to be update in the target table (D.CHANGEDATE).
But which is not happening, since I have the COALESCE statement in UPDATE QUERY for <> '00000000'
This will eliminate C.date3 values(20190906) to update in the target table.
Could you please correct the update query to update C.date3 value to the D.CHANGEDATE.
Thanks in Advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pieterlu SSC-Addicted Points: 498 More actions · Answer 1

Remember that COALESCE will find the first non-NULL value, therefore it will evaluate to true if any one of the values in the list is valid. Look at your other JOIN conditions; perhaps it would be better to join each table separately, with conditions similar to:

for table A:

(A.lin_date_exp NOT IN ('','00000000')

OR A.deact_date NOT IN ('','00000000'))

for table B:

B.npi_deact_date NOT IN ('','00000000')

(or as an alternative to NOT IN, you can still use <>, eg.

B.npi_deact_date <> ''

AND B.npi_deact_date <> '00000000')

To provide a more exact answer (if the above does not help), we would have to see the rest of your JOIN conditions.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Ummmm.... what is the data-type for those date columns in the COALESCE?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saintmount.Sql Ten Centuries Points: 1023 More actions · Answer 3

Hi,

Data-type for those date columns in the COALESCE is varchar 10 and 8.

Thanks,