Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update / Insert

Posted on 2008-10-13
3
Medium Priority
?
339 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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

604 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