A Tricky Update - 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

SQL Server 2000 Forums

SQL Server Development (2000)

A Tricky Update

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

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

Hello Guys
I am burning my brains over an update statement and would appreciatte some help please.

I have two product tables here one that's supplier id is null and another that has proper supplier id's in it, I want to set the table that has null supplier id's to that products respective supplier id without segmenting this table into smaller store specific product tables

my tables are as follows:

--null suppid in itemsales table
TBL A
storeno prodcode suppid
113 10005 null
113 10006 null
181 23411 null
181 100260 null
103 100317 null
103 10005 null
103 100572 null
103 10006 null
--product with proper supplier id

TBL B
prodcode proddescrip supa supb supc
10005 MERLOT 75cl. 99993 100020 99993
23411 SAUMUR BLANC 99993 270025 99993
10006 Hot Pot 21111 73920 21111

store 113 relates to supp a, store 103 relates to supplier b and store 181 realtes to supplier c etc etc...

Can somone please help me with a update statement here please ?
The best I can come up with is something like this :

UPDATE TBLA
SET SuppID = (SELECT b.supa FROM tblb b inner join tbla a on (b.prodcode = a.prodcode) where storenum = 113)

but this is not linking the proper product codes per store ?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-28 : 13:22:58

>> store 113 relates to supp a, store 103 relates to supplier b and store 181 realtes to supplier c etc etc...
What is the relationship with supplier with store ?
or how to derive a relationship with the data u provided?

Srinika

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-29 : 01:30:38

supa supb supc
99993 100020 99993

out of which supcode(whether supa, supb, supc) u wants to update as supplierid for storeno 10005? same condition applies to the remaining stores. coz, the situation, u have mentioned is little bit confussing.
let us know, what u wants to do.

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-29 : 05:40:51

Try to apply this logic

Update T
set T.col=S.col
from TargetTable T inner join SourceTable S on
T.uniqueCol=S.uniqueCol

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-29 : 09:04:58

create table temp(
storeno numeric(10),
prodcode numeric(10),
suppid numeric(10))

insert into temp
select 113, 10005, null union all
select 113, 10006, null union all
select 181, 23411, null union all
select 181, 100260, null union all
select 103, 100317, null union all
select 103, 10005, null union all
select 103, 100572, null union all
select 103, 10006, null

create table source
(prodcode numeric(10),
proddescrip varchar(10),
supa numeric(10),
supb numeric(10),
supc numeric(10))

insert into source
select 10005, 'MERLOTCL', 99993, 100020, 99993 union all
select 23411, 'SAUMURANC', 99993, 270025, 99993 union all
select 10006, 'Hot Pot', 21111, 73920, 21111

Update Temp
set Temp.suppid = Source.supcfrom temp inner join Source on
Temp.prodcode=Source.prodcode

its updating on five of the rows in any case.

select * from temp
o/p

storeno prodcode suppid
113 10005 99993
113 10006 21111
181 23411 99993
181 100260 NULL
103 100317 NULL
103 10005 99993
103 100572 NULL
103 10006 21111

Mahesh

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-29 : 11:18:22

That's the logic I thought I was after Madhi thank-you so I came up with something like this:

Update i
set i.SupplierID = (SELECT JsySfwySuppCode FROM RetailChoiseproduct)
from itemsales i inner join RetailChoiseproduct c on
(i.prodcode = left(c.productcode,6))
Where i.storenum in(113,181)

and then this :

Update i
set i.SupplierID = (SELECT c.JsySfwySuppCode FROM RetailChoiseproduct c inner join itemsales_test i on(i.prodcode = left(c.productcode,6) ))
from itemsales_test i inner join RetailChoiseproduct c on
(i.prodcode = left(c.productcode,6))
Where i.storenum in(113,181)

I am getting subquery returned more than one value error for each of these statements....
this is expected because I am trying to mass update in my table, but what I am finding hard is to see how I can update each specific products supplierid if products have different supplier id's coming out of a single column (JsySfwySuppCode col) in the c table only if the storenum in the i table is 113 or 181,,,,,store 114,123,232,444 all belong to GsySfwySuppCode column in the same c table ..etc.etc.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-31 : 13:17:51

Please help me with this update statement, Please anyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources