SQLTeam.com | Weblogs | Forums

Insert into select case when


#1

Hi, I have a table t1 with 5 columns key,col2,col3,col4,col5 I want to insert data into t1 from another table t2 and depending on a value of a column in t2 insert into either col2 or col3 or col4 or col5 of table t1.

I have written the following query. It gives me error code 1054

insert into t1 (key,col2,col3,col4,col5)
select t2.key,
CASE WHEN count(distinct c.ccount) >= 10 THEN col2 = 1
WHEN count(distinct c.ccount) >= 100 THEN col3 = 1
WHEN count(distinct c.ccount) >= 500 THEN col4 = 1
WHEN count(distinct c.ccount) >= 1000 THEN col5 = 1
end from t2 c;


#2

The following will insert 1 into one of the columns and null into the other columns (as long as count is at least 10. If not it will insert null into all four col2,col3, col4 and col5)

INSERT INTO t1
    (
        [key],
        col2,
        col3,
        col4,
        col5
    )
SELECT
    t2.[key],
	CASE 
		WHEN COUNT(DISTINCT c.ccount) >= 10 
		AND COUNT(DISTINCT c.ccount)  <  100 THEN 1 
	END,
	CASE 
		WHEN COUNT(DISTINCT c.ccount) >= 100
		AND COUNT(DISTINCT c.ccount)  <  500 THEN 1 
	END,
	CASE 
		WHEN COUNT(DISTINCT c.ccount) >= 500
		AND COUNT(DISTINCT c.ccount)  <  1000 THEN 1 
	END,
	CASE 
		WHEN COUNT(DISTINCT c.ccount) >= 1000 THEN 1 
	END
FROM
    t2;

#3

Thank you James!