SQL with ifelse conditon check to run - Stack Overflow

admin管理员组

文章数量:1122846

We have a requirement. WE need run some UPDATE/INSERT SQL however these UPDATE SQL only get run when another condition is TRUE e.g another Table is empty SQL like this

CASE WHEN (SELECT COUNT(1) FROM DataTable2) == 0 THEN 
    UPDATE DataTable SET column1= 1, column2= 'NULL' 
END;

I dont know whether DB platform e.g. Oracle/MSSQL/Postgresql support this type of logic

We have a requirement. WE need run some UPDATE/INSERT SQL however these UPDATE SQL only get run when another condition is TRUE e.g another Table is empty SQL like this

CASE WHEN (SELECT COUNT(1) FROM DataTable2) == 0 THEN 
    UPDATE DataTable SET column1= 1, column2= 'NULL' 
END;

I dont know whether DB platform e.g. Oracle/MSSQL/Postgresql support this type of logic

• sql

Share Improve this question Follow asked Nov 21, 2024 at 15:22 zhengzheng 2966 bronze badges 4

• 3 No need for a case, simply move the select count to the UPDATE's WHERE clause. – jarlh Commented Nov 21, 2024 at 15:24
• 2 column2= 'NULL' assigns a character string to column2. Remove the quotes to get the null value. I.e. column2= NULL. – jarlh Commented Nov 21, 2024 at 15:26
• 1 UPDATE DataTable SET column1= 1, column2= NULL WHERE (SELECT COUNT(1) FROM DataTable2) = 0 – Evya2005 Commented Nov 21, 2024 at 15:47
• @Evya2005 thank you very much this approach work – zheng Commented Nov 22, 2024 at 9:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Maybe MERGE INTO is what you are asking for:

--      S a m p l e    D a t a : 
Create Table tbl1 (ID Number(1), A_NUMBER Number(1), A_LETTER Varchar2(1));
Insert Into tbl1 VALUES(1, 1, 'A'), (2, 2, 'B');

Create Table tbl2 (ID Number(1), T1_ID Number(1), SOME_TEXT Varchar2(32));
Insert Into tbl2 VALUES(1, 1, 'Some text for ID = 1'), (2, 3, 'Some text For ID = 3');

Select * From tbl1;

ID	A_NUMBER	A_LETTER
1	1	A
2	2	B

Select * From tbl2;

ID	T1_ID	SOME_TEXT
1	1	Some text for ID = 1
2	3	Some text For ID = 3

--  Joined data with intended action to be taken
Select t1.ID, t1.A_LETTER, t1.A_NUMBER, 
       Case When t1.ID Is Null Then 'Insert' 
       Else 'Update' 
       End "ACTION", t2.T1_ID, t2.SOME_TEXT
From   tbl2 t2
Left Join tbl1 t1 ON( t1.ID = t2.T1_ID)

ID	A_LETTER	A_NUMBER	ACTION	T1_ID	SOME_TEXT
1	A	1	Update	1	Some text for ID = 1
null	null	null	Insert	3	Some text For ID = 3

--  Using above SQL you can do both, Insert and Update of tbl1 depending on the matching conditions
MERGE INTO tbl1 t USING
    ( Select t1.ID, t1.A_LETTER, t1.A_NUMBER, 
                  Case   When t1.ID Is Null Then 'Insert' 
                  Else ' Update' 
                  End   "ACTION", t2.T1_ID, t2.SOME_TEXT
       From       tbl2 t2
       Left Join  tbl1 t1 ON( t1.ID = t2.T1_ID)
    ) x ON(x.ID = t.ID)
WHEN MATCHED THEN
    UPDATE Set A_LETTER = Null
WHEN NOT MATCHED THEN
    INSERT Values( x.T1_ID, x.T1_ID, 'C');

--  Check the result 
Select * From tbl1;

--      R e s u l t : 
 ID  A_NUMBER  A_LETTER
 --  --------  --------
 1          1  null      -- updated
 2          2  B         -- intact
 3          3  C         -- inserted

fiddle

本文标签： SQL with ifelse conditon check to runStack Overflow