INSERT value from TableA to TableB using WHERE

Question

INSERT value from TableA to TableB using WHERE

2Tall

SSChampion

Points: 14644
More actions
August 19, 2008 at 5:41 am

#197058

Hi. I have imported an excel sheet into SQL as a table. I would like to update a field in an existing table based on a condition.
Insert data from 'FieldA' TableA to 'FieldA' TableB
WHERE TableA.Part = TableB.Part
Would the following be correct:
INSERT INTO [MyDatabase].[dbo].[Products]
(,[Cost])
SELECT
COST
FROM [MyDatabase].[dbo].[PartLastCost]
WHERE Products.Part = PartLastCost.PART
Many thanks,
Phil.
PS: To note Products.Cost does already contain some values against a few products.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

Do you mean to say that the products table has rows and you want to update the cost column . Then you are looking at a update statement not insert.

OR have i totally misunderstood you question....;)

"Keep Trying"

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 2

please clarify whether u need to update or insert new records

lookslike u need to update records already existed based on part column....

2Tall SSChampion Points: 14644 More actions · Answer 3

It should be UPDATE.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 4

Would this work:

UPDATE [Database].[dbo].[Products]

SET Cost = PartLastCost.COST

WHERE Products.Part = PartLastCost.PART

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 5

Took a flyer and executed the query. Receive error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "PartLastCost.PART" could not be bound.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 6

OK embarrassed by my earlier feeble attempts I have now experienced some success:

UPDATE Products

SET dbo.Products.Cost = dbo.PartLastCost.COST

FROM dbo.PartLastCost

INNER JOIN dbo.Products ON dbo.PartLastCost.PART = dbo.Products.ProductId

WHERE Products.Part = PartLastCost.PART

Never updated as many records as I expected however. The table I am copying from has 1766 records, I expected the query to update 1766 records however it only updated 9211?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 7

If you're unsure that you're getting the results you expect from the UPDATE, then just comment out the UPDATE part of the clause and run SELECT * with the rest of the query intact. That allows you to validate that the JOIN operations within the UPDATE are correct.

Another trick is to wrap that in a BEGIN TRANSACTION and ROLLBACK TRANSACTION and then look at the row count to be sure it's right. Or, if it's on a test machine, not production, simply use the BEGIN TRANSACTION, veryify the row count, then run COMMIT TRANSACTION if you're happy. I wouldnt do that on Production systems because it would hold the locks open while you make up your mind.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

2Tall SSChampion Points: 14644 More actions · Answer 8

Hi Grant. The field I am updating contains values, could this be a problem or should my query override?

4177 rows have a value of: 0.00000

There are 17521 rows where the Part number values appear in both tables (145 where the related part is NULL).

I would expect my query to UPDATE 17251 rows.

Should I update all existing values before running the UPDATE? If yes is the syntax SET dbo.Products.Cost = 0.00000

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 9

No, it shouldn't matter that there is already data there. That's the point of the UPDATE. Did you run the query as a select? Does it return the 17k rows or the smaller number?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

2Tall SSChampion Points: 14644 More actions · Answer 10

The following returns 17251 rows:

SELECT PartLastCost.PART, Products.Part

FROM PartLastCost INNER JOIN

Products ON PartLastCost.PART = Products.Part

WHERE Products.Part = PartLastCost.PART

Thanks,

Phil.

Update:

OK now I have the SELECT query that reurns 9211 rows:

SELECT Products.Cost,PartLastCost.COST

FROM PartLastCost

INNER JOIN Products ON PartLastCost.PART = Products.ProductId

WHERE Products.Part = PartLastCost.PART

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 11

OK, I have found my mistake................should all work fine now. My INNER JOIN was referencing the wrong field!!

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 12

Great!

I use that query technique all the time. Verify that I'm getting what I want, then make it into an UPDATE statement. Glad it worked out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

2Tall SSChampion Points: 14644 More actions · Answer 13

Many thanks Grant. I need to adjust my mindset......not a programmer......early days but hope to rise to the challenge.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper