I have to update columns in a table with data. And in one of column's data has & in it like( 'Salt & Pepper').
So when I run the update statement, it is prompting me to enter the value.
But in my case, it should not ask, it should insert the data 'Salt & Pepper' into the table's column.
UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;
How can I do that?
In SQL*Plus, it is SET DEFINE OFF
SQL> UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;
Enter value for pepper:
old 1: UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000
new 1: UPDATE dishes SET ingredient = 'Salt ' WHERE dish_id = 5000
1 row updated.
But:
SQL> set def off
SQL> UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;
1 row updated.
SQL>
Tool you use might have different option, but - generally speaking - it comes to the same thing.
use SET ESCAPE: SET ESC[APE] {OFF | ON | escape_char}
so:
SET ESCAPE ON
SET ESCAPE "\"
UPDATE dishes SET ingredient = 'Salt \& Pepper' WHERE dish_id = 5000;
should do the trick, I think.
