Solved

Update / Insert

Posted on 2008-10-13
3
329 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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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