19

i have a table which has two columns i'd fill one of the columns by selecting other table column data but how can i fill the next column cause i can't use VALUE. Here's the code

INSERT INTO Numbers(number, val) SELECT LaptopID FROM Laptop WHERE Laptop.Pid = 2

as you can see the "val" column left empty how can i fill that?

tereško
  • 58,060
  • 25
  • 98
  • 150
shervin -
  • 353
  • 1
  • 4
  • 12
  • That depends on what you want to put in it. Do you want a default value, a value stored in the `Laptop` table, no value (`NULL`) at all, or something else? *(Also, which variant of SQL? SQL Server, MySQL, Oracle?)* – MatBailie Aug 07 '12 at 15:39
  • u can do following.. INSERT INTO Numbers(number, val) SELECT LaptopID, 1 FROM Laptop WHERE Laptop.Pid = 2 but what u want to insert into second column that would be question. – AJP Aug 07 '12 at 15:41
  • i want to fill the next column with value stored in another table too. this is sql server CE – shervin - Aug 07 '12 at 15:42
  • @shervin- : Which table? How is that table related to the Laptop table *(Is there a PrimaryKey, ForeignKey relationship)*? What field names, etc? – MatBailie Aug 07 '12 at 15:44
  • @Dems if you look at the code below you'll find the answer – shervin - Aug 07 '12 at 15:50
  • @shervin- : The answers that you commented on do not *(as you described in your first comment)* draw the second value from another table. Instead they use a default or hard-coded value. If that is what you need, that's great, but it *is* different from your description. – MatBailie Aug 07 '12 at 15:53
  • @Dems yes dude you're right. that was a bit different. anyway thanks to all of you – shervin - Aug 07 '12 at 15:56

7 Answers7

31

Use NULL if the column allows it:

INSERT INTO Numbers(number, val)
SELECT LaptopID, NULL
FROM Laptop WHERE Laptop.Pid = 2

Or use the intended (hardcoded) value that you want.

If number:

INSERT INTO Numbers(number, val)
SELECT LaptopID, 2
FROM Laptop WHERE Laptop.Pid = 2

or if text:

INSERT INTO Numbers(number, val)
SELECT LaptopID, 'val'
FROM Laptop WHERE Laptop.Pid = 2
aF.
  • 64,980
  • 43
  • 135
  • 198
3

If you don't have a corresponding value that needs to go into number; then you can just put zero or NULL:

Somethign like this---

INSERT INTO numbers (number, val)
SELECT NULL, laptopid
  FROM laptop
 WHERE laptop.pid = 2
Roberto Navarro
  • 948
  • 4
  • 16
1

You would add it as an argument on the SELECT. For example:

INSERT INTO Numbers(number, val)
    SELECT LaptopID, 'val'
    FROM Laptop
    WHERE Laptop.Pid = 2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

IMHO, you need an INNER JOIN with that other table, something like this:

INSERT INTO Numbers(number, val) SELECT L.LaptopID, OT.OTHER_COLUMN FROM Laptop L 
INNER JOIN OTHER_TABLE OT ON L.SOME_COLUMN = OT.ANOTHER_SOME_COLUMN 
WHERE Laptop.Pid = 2
Nathan
  • 2,705
  • 23
  • 28
1

SQLCE does not support Multiple table Update etc, so i used the following method

SELECT Laptop.LaptopID, Table2.val FROM Laptop, Table2 WHERE
Laptop.Pid = 2  or where laptop.pid= table2.pid

then bulk insert using http://sqlcebulkcopy.codeplex.com/

hope it helps :)

chridam
  • 100,957
  • 23
  • 236
  • 235
Nitin
  • 404
  • 5
  • 8
0
INSERT INTO answer(userans) VALUES(OptionA) Select username From answer WHERE username= 'Name';

I want to add a data in a column with reference to another column in the same table.

habib
  • 2,366
  • 5
  • 25
  • 41
shivang bhatnagar
  • 23
  • 4
0

INSERT INTO Numbers(number, val) SELECT LaptopID, '"+yourlabel.Text+"' FROM Laptop WHERE Laptop.Pid = 2

nazer bahaa
  • 1