Need Help with Update 1

[sqlhotdog2005]

when I run just the select portion of this statement I get this error message: Subquery returned more than 1 value.

UPDATE LTCCO..GL40100 SET LTCCO..GL40100.OFFINDX=
(SELECT ACTINDX FROM LTCCO..GL00100 WHERE ACTNUMBR_1=
(SELECT ROCK..GL00100.ACTNUMBR_1 FROM ROCK..GL00100, LTCCO..GL40100
WHERE LTCCO..GL40100.OFFINDX=ROCK.GL00100.ACTINDX))

Can someone help me revise this statement so that LTCCO..GL40100.OFFINDX will be updated properly?

Thank you VERY much for your help!

 

[amrita418]

If there are multiple values for actnumbr_1 which one do you want to pick? Or can you add any other clauses to restrict the select to one value per actindx?

Try using top 1 with an order by clause according to your requirements.

Regards,
AA

 

[sqlhotdog2005]

Thank you for your reply. Allow me to re-phrase my question:

I have 2 databases and 4 tables to consider.

Both tables in Database 1 are correct.
Database 2..Table 1 is correct.
Database 2..table 2 is incorrect. It was simply copied from Database 1.
Now I need to update Database 2..Table 2 with the proper Index according to the Account that is in Database 2/Table 1.

This is how it looks now:

|----------------------
| DATABASE 1
|======================
| Table 1:
| IndexA Account
| ----- -------
| 1 100
| 2 200
|
|-----------------------
| Table 2:
| IndexB UniqueCode
| ----- ----------
| 1 aaa
| 2 bbb
|
|-----------------------

|-----------------------
| DATABASE 2
|=======================
| Table 1:
|
| IndexA Account
| ----- -------
| 4 100
| 5 200
|
| ----------------------
| Table 2:
|
| IndexB UniqueCode
| ----- ----------
| 1 aaa
| 2 bbb
|
|-----------------------


After running the proper command, this is how it SHOULD look:

|-----------------------
| DATABASE 1
|=======================
| Table 1:
| IndexA Account
| ----- -------
| 1 100
| 2 200
|
|-----------------------
| Table 2:
| IndexB UniqueCode
| ----- ----------
| 1 aaa
| 2 bbb
|
|-----------------------

|-----------------------
| DATABASE 2
|=======================
| Table 1:
|
| IndexA Account
| ----- -------
| 4 100
| 5 200
|
|-----------------------
| Table 2:
|
| IndexB UniqueCode
| ----- ----------
| 4 aaa
| 5 bbb
|
|-----------------------

So far I've got this code:

UPDATE Database2..Table2 SET Database2..Table2.IndexB=
(SELECT IndexA FROM Database2..Table1 WHERE Account=
(SELECT Database1..Table1.Account FROM Database1..Table1, Database2..Table2
WHERE Database2..Table2.IndexB=Database1.Table1.IndexA))

But it's not working..

Maybe I need a JOIN in there somewhere(?), and a WHERE... something like WHERE Database2..Table2.UniqueCode = Database1.Table2.UniqueCode...

I really need some help here.... I've been working on this for hours!

Thank you for any help you can provide!

 

[amrita418]

Here you go:

create table #DB1Table1 (IndexA int, Account int)
insert into #DB1Table1 values (1, 100)
insert into #DB1Table1 values (2, 200)

create table #DB1Table2(IndexB int, UniqueCode varchar(5))
insert into #DB1table2 values (1, 'aaa')
insert into #DB1table2 values (2, 'bbb')

create table #DB2Table1 (IndexA int, Account int)
insert into #DB2Table1 values (4, 100)
insert into #DB2Table1 values (5, 200)

create table #DB2Table2(IndexB int, UniqueCode varchar(5))
insert into #DB2table2 values (1, 'aaa')
insert into #DB2table2 values (2, 'bbb')

update  d 
set 	d.IndexB = a.indexA
from 	#DB2Table1 a 
        inner join #DB1Table1 b 
	on a.account = b.account
	inner join #DB1Table2 c
	on (b.IndexA = c.IndexB)
	inner join #Db2Table2 d
	on (c.uniquecode = d.uniquecode)

Regards,
AA

 

[sqlhotdog2005]

Yowza! I think that will do it! I've run it under a test datbase with test data... and will be able to run it on the production system in just a little while.

You definitely get a star... and just curious: On a scale of 1 to 10 for somebody with your obvious skills... how tough was that? Joins are complicated enough for me... not to mention a statement with three of them. As I read the code, I can "follow" it, but I still don't really "understand" it... I'll post again after I've run it for real.

Thank you!

 

[sqlhotdog2005]

Yep... looks like it worked just fine... thank you again a BUNCH for your help!

 

[amrita418]

To tell you the truth, you were almost there. Also you knew you had to use the join condition so there was nothing really left. I just added the join condition to update the respective rows (instead of the select).

Good luck.
Regards,
AA

 

[sqlhotdog2005]

You're too modest... thanks again.