How about this one...I have two tables with customer IDs and flags. I may have the same customer in both tables - the flag I want to end up with should be the non null value (there cannot be different non null values across the tables.I have done this using the following:-create table test1 (urn varchar(10), flag1 varchar(10),flag2 varchar(10))create table test2 (urn varchar(10), flag1 varchar(10),flag2 varchar(10))insert into test1 values (1,'L',null)insert into test1 values (2,'N',null)insert into test2 values (1,null,'S')insert into test2 values (3,'N',null)select * into test3 from test1unionselect * from test2select URN,flag1=max(flag1),flag2=max(flag2) into test4 from test3 group by urn having count(*)>1delete from test3 where exists (select * from test4 where test3.urn=test4.urn)select * into test5 from test3unionselect * from test4
This is the result (contents of test5):-urn flag1 flag2=== ===== =====1 L S2 N NULL3 N NULLCan I do this somehow using coalesce in a single select? PS. I am trying to avoid updates as I am working with 3 million + recordsEdited by - davidpardoe on 07/18/2001 11:16:59Edited by - davidpardoe on 07/18/2001 11:17:49
Does the Coalesce function solve this?
Posted - 2001-07-18 : 10:05:27