INSERT INTO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT INTO

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:27:46

Many forms of the statement...

What's wrong with my syntax? Just want to copy one column into another table's empty column, using this:

insert into prc_price_recs(pr_sort_key1)
select pr_usrchar1
from prc_price_recs2

Getting error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'PR_PRIMARY', table 'FINANCE_MIKE.dbo.PRC_PRICE_RECS'; column does not allow nulls. INSERT fails.
The statement has been terminated.

But I clearly don't want to insert that into the PR_PRIMARY column!! I wanna put it into pr_sort_key1

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:29:45

You need SET UPDATE WHERE, not INSERT INTO
INSERT INTO creates a new row, whereas SET UPDATE WHERE appends an existing row

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-12 : 05:31:58

The Column pr_sort_key1 is PK so it does't all NULL values in your result.

Possible u avoid NULL in your select.

insert into prc_price_recs(pr_sort_key1)
select pr_usrchar1
from prc_price_recs2 where pr_usrchar1 is not NULL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:32:03

Oh...

Maybe this command needs to update that special column at the same time, as it's special as a kind of reference column... to tie in with any other column changes....

Good to know these things.

So how do I go about changing that one column I want to change?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-12 : 05:33:49

If u have data in prc_price_recs table?

If yes truncate it and TRY!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:36:46

Thank You Mr Holty!

I always make assumptions and see these problems coming, when I do these things about once every year! Good to test on backups first...

I do complex reporting. Don't make changes.

UPDATE it is

Cheers.

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:38:19

Welcome you are

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:39:40

Of course I meant UPDATE SET WHERE, not SET UPDATE WHERE, but I think you knew that.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:46:53

Why can't I use this? The f$%^&£ parser said OK. It needs to be a row-by-row alteration. What's the syntax?

update prc_price_recs
set pr_sort_key1 = (select pr_usrchar1 from financetemplate.dbo.prc_price_recs2)

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:53:27

Its because your

select pr_usrchar1 from financetemplate.dbo.prc_price_recs2

returned a row for each record in the prc_price_recs2 table. You need to do it row by row.

Not only that but when you're updating, you need to do it row by row so you need 1 row returned from your subquery, PLUS you need some kind of identifier so it can be realted to the row you're updating, PLUS you need a WHERE clause in your UPDATE statement so you know which row to update.

Something like. .

update prc_price_recs
set pr_sort_key1 = (select pr_usrchar1, TABLE_ID_COLUMN from financetemplate.dbo.prc_price_recs2)
where TABLE2_ID_COLUMN = TABLE_ID_COLUMN

See what I mean?

Google it or try the MSDN website
http://msdn.microsoft.com/en-us/library/aa299742(SQL.80).aspx

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:55:29

Now using this.....

update prc_price_recs
set pr_sort_key1 = financetemplate.dbo.prc_price_recs2.pr_usrchar1
where financetemplate.dbo.prc_price_recs2.prcode2 = prc_price_recs.prcode

getting

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "financetemplate.dbo.prc_price_recs2.prcode2" could not be bound.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 05:57:22

Multipart...

I thought you could have four dots in there

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-12 : 09:27:44

try this


update p
set p.pr_sort_key1 = q.pr_usrchar1
from prc_price_recs p
inner join financetemplate.dbo.prc_price_recs2 q on prcode2= p.prcode
where q.pr_usrchar1 is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources