Help for update table

Question

Help for update table

adonetok

SSCrazy Eights

Points: 8395
More actions
November 13, 2014 at 10:40 am

#292553

In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

adonetok (11/13/2014)
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END

You can't refer to table columns in an IF statement. (Think about it, what row should the IF statement look at?)

You could do this:

IF EXISTS(SELECT 1 FROM dbo.OrderB WHERE OrderCity = '')

BEGIN

...

But, what is it you are really trying to achieve?

[/code]

Gerald Britton, Pluralsight courses

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I would recommend doing yourself a huge favor. You have two major issues with your insert statements. The first is you don't specify the columns. That means you are expecting to know exactly what columns are in the table. If the table structure changes, so does your code. Then you are using select * as the source. This has the same issue. If you change that table your code breaks. Take the extra minute to list all the columns on both sides. Get into this habit and do it every time.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Why do you want to use an IF? That's why we have WHERE clauses.

UPDATE dbo.OrderB

SET OrderCity = 'London'

WHERE OrderCity = ''

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

If you are explicit with your columns you could deal with this in the insert statement. Then the update statement is not needed.

Something like this.

insert into OrderB

(

City

, [OtherColumnsHere]

)

select

case when City = '' then 'London' else City end