Update statement - 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

General SQL Server Forums

New to SQL Server Programming

Update statement

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-03-28 : 16:47:50

I have two tables:

Table A
Itemnumber, ListPrice

Table B
Itemnumber, ListPrice, decplaces, currencyid, etc.

I want an update statement that updates Table B with the 'new list price' in table A - some items will be in table A and not Table B and vice versa.

I tried:

Update TableB set Listprice = TableA.ListPrice
from TableB join TableA on TableA.Itemnumber = TableB.Itemnumber
where TableA.Itemnumber = TableB.Itemnumber

it didn't work -

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 16:55:48

Check Update in BOL

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 17:05:19

"it didn't work"
Can you elaborate more ?

Try this

Update b
set    b.Listprice = a.ListPrice
from   TableB b join TableA a
on     a.Itemnumber = b.Itemnumber
where  a.ListPrice <> b.ListPrice

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 17:07:13

quote:
Originally posted by Srinika

Check Update in BOL

Srinika

Srinika,

How does that help the poster, especially in the "New to SQL Server" forum? They already have tried an update statement, which didn't work.

btamulis, could you explain why your update statement doesn't work? What values isn't it catching?

Tara Kizer
aka tduggan

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-03-28 : 17:18:42

Thank you khtan - your script worked.

I had tried BOL - i find the verbage difficult since i have no formal SQL training.

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-03-28 : 17:35:04

my original update statement returned an error message
"Tables or functions 'TableB' and 'TableB' have the same exposed names. Use correlation names
to distinguish them"

I get very confused in SQL when its appropriate to use a 'join' versus a 'union'. Most of the time it takes me several attempts to successfully update one table based on the values in a different table. Most times I am trying to update SQL ERP Tables with data from an excel spreadsheet. I typically use DTS to create an SQL table in same DB and then try SQL-Transact to populate tables.

Help is always appreciated. This forum has been very good to me.

Thanks again......

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 20:11:56

quote:
Originally posted by tkizer

quote:
Originally posted by Srinika

Check Update in BOL

Srinika

Srinika,

How does that help the poster, especially in the "New to SQL Server" forum? They already have tried an update statement, which didn't work.

btamulis, could you explain why your update statement doesn't work? What values isn't it catching?

Tara Kizer
aka tduggan

If he search the BOL for update, he'll find the following
------------------------------------------------------------
Subqueries in UPDATE, DELETE, and INSERT Statements
Subqueries can be nested in UPDATE, DELETE, and INSERT statements, as well as in SELECT statements.

The following query doubles the price of all books published by New Moon Books. The query updates the titles table; its subquery references the publishers table.

UPDATE titles
SET price = price * 2
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE pub_name = 'New Moon Books')

Here's an equivalent UPDATE statement using a join:

UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
   AND pub_name = 'New Moon Books'

------------------------------------------------------------

In which his un-explained problem also exists
He has the choice of selecting to suite his need

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 20:35:06

Srinika, the information that you posted this time is perfect for this newbie forum. Newbies will not know which section is relevant in BOL and which is not. We need to be a little extra helpful in this forum as it's understood that they do not have much experience with T-SQL yet.

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 20:46:34

Ok Tara,
Thanks for pointing out my mistake.
Accepted the advice.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources