Insert nto command trouble 1

[proteome]

How can I insert data from a query into a table that already exists. I have tried using the insert into command but it seems to add the data from the query into the appropriate column but below blank rows.


table1 before sql statement:

col1 col2
data1
data2
data3

After following sql statement:
INSERT INTO table11 (col2) SELECT (col2) FROM query1

col1 col2
data1
data2
data3
stuff1
stuff2
stuff3

I would like to see the sql statement form the following

col1 col2
data1 stuff1
data2 stuff2
data3 stuff3

Thanks in advance

 

[SQLDenis]

you have to use update not insert
what is the key that you can join on?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[proteome]

There is no key set up at the moment. The data in col1 of table1 can be found also in col1 of query 1.

 

[SQLDenis]

update t1 set t1.col2 =q1.col2
from table1 t1
join query1 q1 on t1.col1 =q1.col1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[proteome]

thanks I shall give it a try

 

[proteome]

question regaring solution

from table1 t1
join query1 q1 on t1.col1 =q1.col1


I am confused about the statement table1 t1 and query1 q1 what does that mean?

should it be

From table1
join query1 on table1.col1 = query1.col1

 

[SQLDenis]

t1 and q1 are aliases so that you can write
join query1 q1 on t1.col1 =q1.col1
instead of
join query1 on table1.col1 =query1.col1

makes shorter sentences and is easier on the eyes especially if you have a lot off tables and columns to join

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[proteome]

I am recieving an error (missing operator in query expression) any clues?

error:
Additional information: Syntax error (missing operator) in query expression 'q1.col2 FROM table1 t1 JOIN query1 q1 ON t1.col1 = q1.col1'.

 

[SQLDenis]

it should work
run this


create table table1 (col1 int,col2 int)
create table query1 (col1 int,col2 int)

insert into table1
select 1,null

insert into query1
select 1,5

select * from table1


update t1 set t1.col2 =q1.col2
from table1 t1
join query1 q1 on t1.col1 =q1.col1


select * from table1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[proteome]

error:

insert into table1
select 1,null

no destination field name in insert into statement(1)

 

[SQLDenis]

??
are you running sql server 2000 and are you running this in QA?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[proteome]

no I am not running SQL server just access

 

[proteome]

Sorry for all the confusion I have finally got it working here is the solution (I guess syntax is different??)

You cannot use the query in the update statement. you get an error about the "Operation must be an updateable query" So make sure both are tables and use the following

Update table1 INNER JOIN table2 ON table1.Reference = table2.Reference SET table1.expNumber1 = table2.expNumber1


Thanks for the help!!!!!