Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

simple query (insert into)

Posted on 2005-04-19
20
Medium Priority
?
257 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:honda99
  • 10
  • 5
  • 5
20 Comments
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 100 total points
ID: 13818302
Is this what you're trying to do:

UPDATE MyTable
SET Opt2 = Opt
0
 

Author Comment

by:honda99
ID: 13818314
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13818321
Can you please post your update statement.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:honda99
ID: 13818324
update mytable
set opt2 = (select opt from mytable
WHERE     opt = '0200')
0
 

Author Comment

by:honda99
ID: 13818331
the total rows i have is 262 rows needs to update/insert

from opt column --> to  opt2 column
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13818334
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13818339
Then this should work:

UPDATE MyTable
SET Opt2 = Opt
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13818344
just use rafrancisco's code:
UPDATE MyTable
SET Opt2 = Opt
0
 

Author Comment

by:honda99
ID: 13818347
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
 

Author Comment

by:honda99
ID: 13818351
i have where caluse need to be passed
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13818352
Try this one:

UPDATE MyTable
SET Opt2 = Opt
WHERE Opt = '0200'
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13818355
UPDATE MyTable
SET Opt2 = Opt
where Opt = '0200'
0
 

Author Comment

by:honda99
ID: 13818371
okay how do i delete those values from opt where opt = 0020 ?

0
 

Author Comment

by:honda99
ID: 13818381
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
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13818382
do you want to delete the entire row where opt = '0020' or just remove the values in the opt column?
0
 

Author Comment

by:honda99
ID: 13818400
just remove the values from the opt column
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13818405
UPDATE MyTable
SET Opt = '' --you can use NULL as well
where Opt = '0020'
0
 

Author Comment

by:honda99
ID: 13818411
how do i use <NULL> ? instead of space
0
 

Author Comment

by:honda99
ID: 13818422
update mytable
set opt = null
where opt = '0020'

??
0
 
LVL 9

Accepted Solution

by:
sudheeshthegreat earned 400 total points
ID: 13818424
UPDATE MyTable
SET Opt = NULL
where Opt = '0020'
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question