Solved

Update / Insert

Posted on 2008-10-13
3
319 Views
Last Modified: 2012-05-05
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.







0
Comment
Question by:nakulamin
3 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 22701138
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
 
LVL 17

Accepted Solution

by:
HuyBD earned 500 total points
ID: 22701146
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
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22701144
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

860 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