Solved

Update / Insert

Posted on 2008-10-13
3
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how the fundamental information of how to create a table.

730 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