0

hi everyone

i have to 4 table and two different database

first db = rotadb

first table =Rota

rota        pRotabos  ldchangetime
101           6         NULL
102           7         NULL
103           8         NULL
104           9         NULL
105          10         NULL

-------
second table = Rotabos

Rotabos_rowid    pRotaBa
6                 1 
7                 2
8                 3
9                 4 
10                5

-------

third table = RotaBa

RotaBa_rowid    skod     
1                201
2                202
3                203
4                204
5                205

-----------------------------------
and other database =dentime

first table =den

den_rowid   skod        time
21           201          60
22           202          62
23           203          63    
24           204          64
25           205          65

and mycode =

update [rotadb].[dbo].[Rota] set [rotadb].[dbo].[Rota].[ldchangetime] = [dentime].[dbo].[den].[time] 
from [dentime].[dbo].[den],[rotadb].[dbo].[Rota] where pRotaBos = (


SELECT [rotadb].[dbo].[RotaBos].[Rotabos_rowid] from [rotadb].[dbo].[RotaBos]
where  [rotadb].[dbo].[Rotabos].pRotaBa=(


SELECT [rotadb].[dbo].[RotaBos].[Rotabos_rowid] from [rotadb].[dbo].[RotaBos]
where  [rotadb].[dbo].[RotaBos].pRotaBa=(

SELECT [rotadb].[dbo].[RotaBa].[RotaBa_rowid] from [dentime].[dbo].[den], [rotadb].[dbo].[RotaBa]
where  [dentime].[dbo].[den].[skod]  = [rotadb].[dbo].[RotaBa].[sKod] )))

ERROR =

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

where am i wrong ?

3
Contributors
5
Replies
6
Views
5 Years
Discussion Span
Last Post by kram54
0

I hope this works.

update [rotadb].[dbo].[Rota],[rotadb].[dbo].[RotaBos], [rotadb].[dbo].[RotaBa],[dentime].[dbo].[den]

  set [rotadb].[dbo].[Rota].[ldchangetime] = [dentime].[dbo].[den].[time] 

where [rotadb].[dbo].[Rota].[pRotaBos] = [rotadb].[dbo].[RotaBos].[Rotabos_rowid]

and [rotadb].[dbo].[RotaBos].[pRotaBa]=[rotadb].[dbo].[RotaBa].[RotaBa_rowid]

and [rotadb].[dbo].[RotaBa].[skod]= [dentime].[dbo].[den].[skod]
0

I doubt that utrivedi's update will work since update should be followed by the table to be updated only.
like this:

update [rotadb].[dbo].[Rota]
set [rotadb].[dbo].[Rota].[ldchangetime] = [dentime].[dbo].[den].[time] 
from [rotadb].[dbo].[Rota],[rotadb].[dbo].[RotaBos], [rotadb].[dbo].[RotaBa],[dentime].[dbo].[den]
where [rotadb].[dbo].[Rota].[pRotaBos] = [rotadb].[dbo].[RotaBos].[Rotabos_rowid]
and [rotadb].[dbo].[RotaBos].[pRotaBa]=[rotadb].[dbo].[RotaBa].[RotaBa_rowid]
and [rotadb].[dbo].[RotaBa].[skod]= [dentime].[dbo].[den].[skod]

But since I feel much more comfortable with the join syntax:

update Rota
set Rota.ldchangetime = den.time 
from [rotadb].[dbo].[Rota] 'Rota' 
inner join [rotadb].[dbo].[RotaBos] 'RotaBos' 
    on Rota.pRotaBos = RotaBos.Rotabow_rowid 
inner join [rotadb].[dbo].[RotaBa] 'RotaBa' 
    on RotaBos.pRotaBa = RotaBa.RotaBa_rowid 
inner join [dentime].[dbo].[den] den 
    on RotaBa.skod = den.skod
0

thank you for help that is work but now it gives another error ' converting to varchar to numeric '

 update [rotadb].[dbo].[Rota]
 set [rotadb].[dbo].[Rota].[ldchangetime] =convert ( numeric(22,2)[dentime].[dbo].[den].[time] )
 from [rotadb].[dbo].[Rota],[rotadb].[dbo].[RotaBos], [rotadb].[dbo].[RotaBa],[dentime].[dbo].[den]
 where [rotadb].[dbo].[Rota].[pRotaBos] = [rotadb].[dbo].[RotaBos].[Rotabos_rowid]
 and [rotadb].[dbo].[RotaBos].[pRotaBa]=[rotadb].[dbo].[RotaBa].[RotaBa_rowid]
 and [rotadb].[dbo].[RotaBa].[skod]= [dentime].[dbo].[den].[skod]

Edited by kram54

0

thank you for helping ..i export to excel file my db than import excel file but i change to dentime.dbo.den.time datatype in dentime.dbo.den.time
now it is ok

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.