Update / Insert

If I am running following code I am gettting error : Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
--------------------------------------------------------------------------------
update scheme.oppickm_archive
SELECT * FROM  scheme.oppickm INNER JOIN scheme.opheadm
ON scheme.opheadm.order_no = scheme.oppickm.order_no
WHERE scheme.opheadm.status IN ('8', '9') AND
scheme.opheadm.date_entered <= dateadd(d, -90, getdate())
========================================================

If I am running following code I am getting this error : Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
----------------------------------------------------------------------------------
INSERT INTO scheme.oppickm_archive
SELECT * FROM  scheme.oppickm INNER JOIN scheme.opheadm
ON scheme.opheadm.order_no = scheme.oppickm.order_no
WHERE scheme.opheadm.status IN ('8', '9') AND
scheme.opheadm.date_entered <= dateadd(d, -90, getdate())

===================================================

Please suggest me any other alternative or correct the above code.







nakulaminAsked:
Who is Participating?
 
HuyBDConnect With a Mentor Commented:
first, you should ensure the number of column of scheme.oppickm_archive and select query is the same, if not, you must specify each field for insert query
INSERT INTO scheme.oppickm_archive
SELECT scheme.oppickm.field1,....
FROM  scheme.oppickm INNER JOIN scheme.opheadm
ON scheme.opheadm.order_no = scheme.oppickm.order_no
WHERE scheme.opheadm.status IN ('8', '9') AND
scheme.opheadm.date_entered <= dateadd(d, -90, getdate())

Open in new window

0
 
OtanaCommented:
Do you want to update exisiting records or insert new ones?

If you want to update, use following syntax (depending on what you need exactly):

UPDATE scheme.oppickm_archive
SET col1 = value1

If you want to insert, make sure the number of colulns you want to insert matches the number of colulns of your table, or use following syntax:

INSERT into scheme.oppickm_archive(col1, col2, col3)
SELECT Value1, Value2, Value3 FROM Table1
0
 
Cvijo123Commented:
in update statment u need to set fields u trying to update

update table
 set field1 = 'sometjhing',
       field2 = 'something new'
from table
 join ....

and in insert statment you are trying to insert more fields that your oppickm_archive have.

When  you use insert statmen always fill fields you are inserting

insert into table (field1, field2, field3)
Select filed1, field2, field3
from table
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.