DJSweetness DJSweetness - 1 year ago 81
SQL Question

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

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.


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

Answer Source

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) );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download