问题描述:

I have a tbl, for example:

uniqueId | col1 | col2 | col3

u1 8

u2

u3 13 89

What I want is to insert into the first empty column (can make them null if that helps). In the given if I'll add to u1 the value 2 it will be inserted into col2. If I do it for u2, it will enter col1. and for u3 it will enter to u3. These three queries will do the trick but I would rather do it in one.

INSERT INTO tbl SET col1 = $toInsertVal WHERE uniqueId=u col1=''

INSERT INTO tbl SET col2 = $toInsertVal WHERE uniqueId=u col1<>'' AND col2=''

INSERT INTO tbl SET col3 = $toInsertVal WHERE uniqueId=u col1<>'' AND col2<>'' AND col3=''

网友答案:
insert into tbl set 
col1 = case when col1 = '' then $toInsertVal else col1 end
, col2 = case when col2 = '' and col1 <> '' then $toInsertVal else col2 end
...
where uniqueid = u

I've ignored NULLs for the sake of simplicity. Basically you can use a CASE on each column, checking on the first empty column and setting its value to its current value if no change is required.

相关阅读:
Top