simple query (insert into)

i have table called mytable

mytable structure is:

id int
opt varchar(4)
type varchar(4)

============
id  |  opt    |  type
============
1     0100     1500
2     0100     1500
3     0200     1500
4     0200     1500
5     0200     1500

i have created one more column called

opt2 varchar(4)

my questions is:

i want to copy all the data from mytable to opt2

insert into mytable opt2
select opt from mytable
where opt = '0200'

but i get the error:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'type', table 'mytable'; column does not allow nulls. INSERT fails.
The statement has been terminated.

? whats wrong here
honda99Asked:
Who is Participating?
 
sudheeshthegreatCommented:
UPDATE MyTable
SET Opt = NULL
where Opt = '0020'
0
 
rafranciscoCommented:
Is this what you're trying to do:

UPDATE MyTable
SET Opt2 = Opt
0
 
honda99Author Commented:
i tried to use update but i get this error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rafranciscoCommented:
Can you please post your update statement.
0
 
honda99Author Commented:
update mytable
set opt2 = (select opt from mytable
WHERE     opt = '0200')
0
 
honda99Author Commented:
the total rows i have is 262 rows needs to update/insert

from opt column --> to  opt2 column
0
 
rafranciscoCommented:
What are you trying to accomplish?  What values do you want to set to opt2 column?  Do you want the value of opt to be assigned to opt2?
0
 
rafranciscoCommented:
Then this should work:

UPDATE MyTable
SET Opt2 = Opt
0
 
sudheeshthegreatCommented:
just use rafrancisco's code:
UPDATE MyTable
SET Opt2 = Opt
0
 
honda99Author Commented:
thats correct

in column opt i have values like

0010
0020
0010
0010
0010
0200
0200
0200
0200
0010
0010
0010
0200

i want to create a new column called opt2 and copy all the values from opt where value = '0200'
0
 
honda99Author Commented:
i have where caluse need to be passed
0
 
rafranciscoCommented:
Try this one:

UPDATE MyTable
SET Opt2 = Opt
WHERE Opt = '0200'
0
 
sudheeshthegreatCommented:
UPDATE MyTable
SET Opt2 = Opt
where Opt = '0200'
0
 
honda99Author Commented:
okay how do i delete those values from opt where opt = 0020 ?

0
 
honda99Author Commented:
its update all rows but i find i have duplicate values
in opt and opt2

so i want to delete the value from opt where opt = 0020

note: without deleting the whole row, just delete the values from opt
0
 
sudheeshthegreatCommented:
do you want to delete the entire row where opt = '0020' or just remove the values in the opt column?
0
 
honda99Author Commented:
just remove the values from the opt column
0
 
sudheeshthegreatCommented:
UPDATE MyTable
SET Opt = '' --you can use NULL as well
where Opt = '0020'
0
 
honda99Author Commented:
how do i use <NULL> ? instead of space
0
 
honda99Author Commented:
update mytable
set opt = null
where opt = '0020'

??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.