SQL: INSERT 1 if value=0 and 0 if value=1

Question

I have data from an SQL query that is 'fileUnavailable' and need to insert into another table that is 'fileAvailable.' The values are either 1 or 0 and I need to do an SQL insert. Since fileUnavaiable and fileAvailable are opposite, I'd need to change the first value to either 1 if it's 0 or 0 if it's 1. Originally, I was thinking to just do an if, else statement to change the value, but that seems bulky and seems too simple for there not to be a method already.

I'm mostly curious as to whether or not SQL has something like !(fileUnavailable) but works for 1 and 0 because these values are ints in my db.

Pseudo:

INSERT INTO table (fileAvailable) VALUES ( NOT($fileUnavailable));

u_mulder · Accepted Answer · 2016-11-04T16:48:04.100

2

Use a hint with ABS:

UPDATE table SET field = ABS(field - 1)

So if field is 1, then field - 1 is 0, abs(0) is still 0.

And if field is 0, then field - 1 is -1, abs(-1) is 1.

ABS() man page.

For query you provided in a question:

INSERT INTO table (fileAvailable) VALUES ( ABS($fileUnavailable - 1) );

edited Nov 04 '16 at 16:48

answered Nov 04 '16 at 16:41

u_mulder

54,101
5
48
64

What if the value is 0 though? Are you saying, use this in an if,else statement? – DJSweetness Nov 04 '16 at 16:44
1

0-1 is -1 and then its ABS is 1 and nope, no if statement is required. This query is complete as it is – Hanky Panky Nov 04 '16 at 16:45
Oh, I understand now! – DJSweetness Nov 04 '16 at 16:47

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

I found this from another stack overflow question.

INSERT INTO `table` SET `my_bool` = NOT my_bool

Update a boolean to its opposite in SQL without using a SELECT

edited May 23 '17 at 11:48

Community

1
1

answered Nov 04 '16 at 17:34

thomasw_lrd

534
3
13

SQL: INSERT 1 if value=0 and 0 if value=1

2 Answers2