sql server new users: update table1 from table2 -- sql server new users

You're in the

sql server new users

group:

update table1 from table2

Matt Williamson
4/17/2006 5:09:23 PM

sql server new users: I'm having way more trouble with this than I should be. There are no
constraints on either table. This is just a temporary data dump to export to
a file.

I just want to update a field in one table (AcctNum) with the same field
from another table using a common column (Name).

ex.

Table 1
ActNum, Name

Table 2
Name, Address, Phone

I want the output to be

Table 2 (or table 3, I don't care)
ActNum, Name, Address, Phone

Do I need an Insert or Update query for this? I've tried both with no luck.
I've simplified this tremendously, but if someone can show me the best way
to acheive my sample, I can take it from there.

TIA

Matt

Re: update table1 from table2

David Portas
4/17/2006 10:33:43 PM

[quoted text, click to view]

A guess:

UPDATE table1
SET acctnum =
(SELECT acctnum
FROM table2
WHERE table2.name = table1.name) ;

This will fail with an error if name isn't unique in table2. What do you
want to do if there is more than one row (and therefore maybe more than one
acctnum) for a given name?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: update table1 from table2

Hugo Kornelis
4/17/2006 11:25:45 PM

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Matt,

SELECT t1.ActNum, t1.Name, t2.Address, t2.Phone
FROM Table1 AS t1
INNER JOIN Table2 AS t2
ON t2.Name = t1.Name

You can insert the output of this in a new table, if you must.

--

Re: update table1 from table2

David Portas
4/18/2006 6:12:28 AM

[quoted text, click to view]

Depends which one you want to keep. If there is more than one Acctnum
you could use some combination of MIN or MAX or TOP. I can't guess
which works for you any more than SQL Server can. It's a data quality /
requirements issue rather than a programming one.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: update table1 from table2

Matt Williamson
4/18/2006 9:04:13 AM

[quoted text, click to view]

That's the problem.

I ran this query

SELECT NameAcctNumber.*
FROM NameAcctNumber JOIN (
SELECT AccountNo
FROM NameAcctNumber
GROUP BY AccountNo
HAVING COUNT(*) > 1
) AS A ON NameAcctNumber.AccountNo = A.AccountNo
order by nameAcctNumber.AccountNo

and returned 53 records. Once I clean these up, my update should work.

[quoted text, click to view]

I'd like to only keep one, but I don't see any way to do that besides
manually deleting them. Do you have any suggestions as to what options I
might have for that?

Thanks

Matt

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn