Solved

MSSQL Update Data From SAME Table

Posted on 2014-09-30
24
119 Views
Last Modified: 2014-10-01
Please see my question below...which was uploaded as a '.PDF' image...Thank You!EE-MSSQL-Question.pdf
0
Comment
Question by:datatechcorp
  • 11
  • 9
  • 2
  • +2
24 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Does this return a result?

select top 1 t2.MIN_QTY
 FROM DBO.IM_INV_CELL t2
 where t2.LOC_ID='1' and
 t2.MIN_QTY<>0 and
t2.LOC_ID='2' AND t2.ITEM_NO='WOL-W03123'

Open in new window


Essentially the sub query is returning null.  you can add an isnull to get around it, but first understand why you're getting the null.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Kyle...

No, it doesn't.  It comes up with "0 rows affected.
0
 
LVL 14

Expert Comment

by:ThomasMcA2
Comment Utility
I think the error is misleading. You can't compare null fields, and it looks like DIM_3_UPR is null.

If DIM_3_UPR is always null, then remove it from your nested query.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Is it possible DIM_3_UPR is NULL?   Not sure what the "*" in the display means.

UPDATE t
SET t.MIN_QTY = (
    select top (1) t2.MIN_QTY
    FROM DBO.IM_INV_CELL t2
    where t2.LOC_ID='1' and
    t2.MIN_QTY<>0 and
    t2.DIM_1_UPR = t.DIM_1_UPR and
    t2.DIM_2_UPR = t.DIM_2_UPR and
    ((t2.DIM_3_UPR IS NULL and t.DIM-3_UPR IS NULL) or (t2.DIM_3_UPR = t.DIM_3_UPR)
    )
FROM dbo.IM_INV_CELL t
where t.LOC_ID='2' AND t.ITEM_NO='WOL-W03123'
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
My thought is that you don't have any rows that match that criteria and have a non-zero min_qty value.  Returning no rows is equivalent to null, and fails the update.  Also, I feel like the subquery should probably be correlated to the outer query, but I would probably do this with a self join.  Also, are you guaranteed that the values of dim_1_upr, dim_2_upr and dim_3_upr are going to exist as a discrete unit for loc_id 1 and 2 ?
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Scott...

The value of '*' in the DIM_3_UPR field...is a valid value (it is, literally, an ASTERISK character).  I'm pasting in the values of ALL of these fields below, so that y'all may see...that there are *NO* <NULL> values anywhere in this dataset.  I'm truly stumped here.

Also, just so you're aware, that query that you gave me, returns the "same* error message with regard to the "NULLS".  Oy!  Any thoughts anyone?  Please help!  Thanks!...Mark

FYI...these are SHOES...where 'DIM_1_UPR' is a SIZE dimension...'DIM_2_UPR' is a WIDTH dimension...and since there's no third dimension...'DIM_3_UPR' becomes, in effect, a wildcard.  That's just the way the software was architected.


item_no      loc_id      dim_1_upr      dim_2_upr      dim_3_upr      min_qty      max_qty
WOL-W03123      1      07.0      M      *      0.0000      0.0000
WOL-W03123      1      07.0      W      *      0.0000      0.0000
WOL-W03123      1      07.5      M      *      0.0000      0.0000
WOL-W03123      1      07.5      W      *      0.0000      0.0000
WOL-W03123      1      08.0      M      *      0.0000      0.0000
WOL-W03123      1      08.0      W      *      2.0000      4.0000
WOL-W03123      1      08.5      M      *      0.0000      0.0000
WOL-W03123      1      08.5      W      *      1.0000      3.0000
WOL-W03123      1      09.0      M      *      0.0000      0.0000
WOL-W03123      1      09.0      W      *      0.0000      0.0000
WOL-W03123      1      09.5      M      *      0.0000      0.0000
WOL-W03123      1      09.5      W      *      0.0000      0.0000
WOL-W03123      1      10.0      M      *      0.0000      0.0000
WOL-W03123      1      10.0      W      *      0.0000      0.0000
WOL-W03123      1      10.5      M      *      0.0000      0.0000
WOL-W03123      1      10.5      W      *      1.0000      1.0000
WOL-W03123      1      11.0      M      *      0.0000      0.0000
WOL-W03123      1      11.0      W      *      0.0000      0.0000
WOL-W03123      1      11.5      M      *      0.0000      0.0000
WOL-W03123      1      11.5      W      *      0.0000      0.0000
WOL-W03123      1      12.0      M      *      0.0000      0.0000
WOL-W03123      1      12.0      W      *      2.0000      3.0000
WOL-W03123      1      13.0      M      *      0.0000      0.0000
WOL-W03123      1      13.0      W      *      3.0000      4.0000
WOL-W03123      1      14.0      M      *      0.0000      0.0000
WOL-W03123      1      14.0      W      *      0.0000      0.0000
WOL-W03123      1      15.0      M      *      0.0000      0.0000
WOL-W03123      1      15.0      W      *      0.0000      0.0000
WOL-W03123      2      07.0      M      *      0.0000      0.0000
WOL-W03123      2      07.0      W      *      0.0000      0.0000
WOL-W03123      2      07.5      M      *      0.0000      0.0000
WOL-W03123      2      07.5      W      *      0.0000      0.0000
WOL-W03123      2      08.0      M      *      0.0000      0.0000
WOL-W03123      2      08.0      W      *      0.0000      0.0000
WOL-W03123      2      08.5      M      *      0.0000      0.0000
WOL-W03123      2      08.5      W      *      0.0000      0.0000
WOL-W03123      2      09.0      M      *      0.0000      0.0000
WOL-W03123      2      09.0      W      *      0.0000      0.0000
WOL-W03123      2      09.5      M      *      0.0000      0.0000
WOL-W03123      2      09.5      W      *      0.0000      0.0000
WOL-W03123      2      10.0      M      *      0.0000      0.0000
WOL-W03123      2      10.0      W      *      0.0000      0.0000
WOL-W03123      2      10.5      M      *      0.0000      0.0000
WOL-W03123      2      10.5      W      *      0.0000      0.0000
WOL-W03123      2      11.0      M      *      0.0000      0.0000
WOL-W03123      2      11.0      W      *      0.0000      0.0000
WOL-W03123      2      11.5      M      *      0.0000      0.0000
WOL-W03123      2      11.5      W      *      0.0000      0.0000
WOL-W03123      2      12.0      M      *      0.0000      0.0000
WOL-W03123      2      12.0      W      *      0.0000      0.0000
WOL-W03123      2      13.0      M      *      0.0000      0.0000
WOL-W03123      2      13.0      W      *      0.0000      0.0000
WOL-W03123      2      14.0      M      *      0.0000      0.0000
WOL-W03123      2      14.0      W      *      0.0000      0.0000
WOL-W03123      2      15.0      M      *      0.0000      0.0000
WOL-W03123      2      15.0      W      *      0.0000      0.0000
WOL-W03123      3      07.0      M      *      0.0000      0.0000
WOL-W03123      3      07.0      W      *      0.0000      0.0000
WOL-W03123      3      07.5      M      *      0.0000      0.0000
WOL-W03123      3      07.5      W      *      0.0000      0.0000
WOL-W03123      3      08.0      M      *      0.0000      0.0000
WOL-W03123      3      08.0      W      *      0.0000      0.0000
WOL-W03123      3      08.5      M      *      0.0000      0.0000
WOL-W03123      3      08.5      W      *      0.0000      0.0000
WOL-W03123      3      09.0      M      *      0.0000      0.0000
WOL-W03123      3      09.0      W      *      0.0000      0.0000
WOL-W03123      3      09.5      M      *      0.0000      0.0000
WOL-W03123      3      09.5      W      *      0.0000      0.0000
WOL-W03123      3      10.0      M      *      0.0000      0.0000
WOL-W03123      3      10.0      W      *      0.0000      0.0000
WOL-W03123      3      10.5      M      *      0.0000      0.0000
WOL-W03123      3      10.5      W      *      0.0000      0.0000
WOL-W03123      3      11.0      M      *      0.0000      0.0000
WOL-W03123      3      11.0      W      *      0.0000      0.0000
WOL-W03123      3      11.5      M      *      0.0000      0.0000
WOL-W03123      3      11.5      W      *      0.0000      0.0000
WOL-W03123      3      12.0      M      *      0.0000      0.0000
WOL-W03123      3      12.0      W      *      0.0000      0.0000
WOL-W03123      3      13.0      M      *      0.0000      0.0000
WOL-W03123      3      13.0      W      *      0.0000      0.0000
WOL-W03123      3      14.0      M      *      0.0000      0.0000
WOL-W03123      3      14.0      W      *      0.0000      0.0000
WOL-W03123      3      15.0      M      *      0.0000      0.0000
WOL-W03123      3      15.0      W      *      0.0000      0.0000
WOL-W03123      4      07.0      M      *      0.0000      0.0000
WOL-W03123      4      07.0      W      *      0.0000      0.0000
WOL-W03123      4      07.5      M      *      0.0000      0.0000
WOL-W03123      4      07.5      W      *      0.0000      0.0000
WOL-W03123      4      08.0      M      *      0.0000      0.0000
WOL-W03123      4      08.0      W      *      0.0000      0.0000
WOL-W03123      4      08.5      M      *      0.0000      0.0000
WOL-W03123      4      08.5      W      *      0.0000      0.0000
WOL-W03123      4      09.0      M      *      0.0000      0.0000
WOL-W03123      4      09.0      W      *      0.0000      0.0000
WOL-W03123      4      09.5      M      *      0.0000      0.0000
WOL-W03123      4      09.5      W      *      0.0000      0.0000
WOL-W03123      4      10.0      M      *      0.0000      0.0000
WOL-W03123      4      10.0      W      *      0.0000      0.0000
WOL-W03123      4      10.5      M      *      0.0000      0.0000
WOL-W03123      4      10.5      W      *      0.0000      0.0000
WOL-W03123      4      11.0      M      *      0.0000      0.0000
WOL-W03123      4      11.0      W      *      0.0000      0.0000
WOL-W03123      4      11.5      M      *      0.0000      0.0000
WOL-W03123      4      11.5      W      *      0.0000      0.0000
WOL-W03123      4      12.0      M      *      0.0000      0.0000
WOL-W03123      4      12.0      W      *      0.0000      0.0000
WOL-W03123      4      13.0      M      *      0.0000      0.0000
WOL-W03123      4      13.0      W      *      0.0000      0.0000
WOL-W03123      4      14.0      M      *      0.0000      0.0000
WOL-W03123      4      14.0      W      *      0.0000      0.0000
WOL-W03123      4      15.0      M      *      0.0000      0.0000
WOL-W03123      4      15.0      W      *      0.0000      0.0000
WOL-W03123      5      07.0      M      *      0.0000      0.0000
WOL-W03123      5      07.0      W      *      0.0000      0.0000
WOL-W03123      5      07.5      M      *      0.0000      0.0000
WOL-W03123      5      07.5      W      *      0.0000      0.0000
WOL-W03123      5      08.0      M      *      0.0000      0.0000
WOL-W03123      5      08.0      W      *      0.0000      0.0000
WOL-W03123      5      08.5      M      *      0.0000      0.0000
WOL-W03123      5      08.5      W      *      0.0000      0.0000
WOL-W03123      5      09.0      M      *      0.0000      0.0000
WOL-W03123      5      09.0      W      *      0.0000      0.0000
WOL-W03123      5      09.5      M      *      0.0000      0.0000
WOL-W03123      5      09.5      W      *      0.0000      0.0000
WOL-W03123      5      10.0      M      *      0.0000      0.0000
WOL-W03123      5      10.0      W      *      0.0000      0.0000
WOL-W03123      5      10.5      M      *      0.0000      0.0000
WOL-W03123      5      10.5      W      *      0.0000      0.0000
WOL-W03123      5      11.0      M      *      0.0000      0.0000
WOL-W03123      5      11.0      W      *      0.0000      0.0000
WOL-W03123      5      11.5      M      *      0.0000      0.0000
WOL-W03123      5      11.5      W      *      0.0000      0.0000
WOL-W03123      5      12.0      M      *      0.0000      0.0000
WOL-W03123      5      12.0      W      *      0.0000      0.0000
WOL-W03123      5      13.0      M      *      0.0000      0.0000
WOL-W03123      5      13.0      W      *      0.0000      0.0000
WOL-W03123      5      14.0      M      *      0.0000      0.0000
WOL-W03123      5      14.0      W      *      0.0000      0.0000
WOL-W03123      5      15.0      M      *      0.0000      0.0000
WOL-W03123      5      15.0      W      *      0.0000      0.0000
WOL-W03123      99      07.0      M      *      0.0000      0.0000
WOL-W03123      99      07.0      W      *      0.0000      0.0000
WOL-W03123      99      07.5      M      *      0.0000      0.0000
WOL-W03123      99      07.5      W      *      0.0000      0.0000
WOL-W03123      99      08.0      M      *      0.0000      0.0000
WOL-W03123      99      08.0      W      *      0.0000      0.0000
WOL-W03123      99      08.5      M      *      0.0000      0.0000
WOL-W03123      99      08.5      W      *      0.0000      0.0000
WOL-W03123      99      09.0      M      *      0.0000      0.0000
WOL-W03123      99      09.0      W      *      0.0000      0.0000
WOL-W03123      99      09.5      M      *      0.0000      0.0000
WOL-W03123      99      09.5      W      *      0.0000      0.0000
WOL-W03123      99      10.0      M      *      0.0000      0.0000
WOL-W03123      99      10.0      W      *      0.0000      0.0000
WOL-W03123      99      10.5      M      *      0.0000      0.0000
WOL-W03123      99      10.5      W      *      0.0000      0.0000
WOL-W03123      99      11.0      M      *      0.0000      0.0000
WOL-W03123      99      11.0      W      *      0.0000      0.0000
WOL-W03123      99      11.5      M      *      0.0000      0.0000
WOL-W03123      99      11.5      W      *      0.0000      0.0000
WOL-W03123      99      12.0      M      *      0.0000      0.0000
WOL-W03123      99      12.0      W      *      0.0000      0.0000
WOL-W03123      99      13.0      M      *      0.0000      0.0000
WOL-W03123      99      13.0      W      *      0.0000      0.0000
WOL-W03123      99      14.0      M      *      0.0000      0.0000
WOL-W03123      99      14.0      W      *      0.0000      0.0000
WOL-W03123      99      15.0      M      *      0.0000      0.0000
WOL-W03123      99      15.0      W      *      0.0000      0.0000
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

Please see the response I just posted to Scott.  As you can see...there is "VALID" (NON-Null) data...in ALL those fields...and, indeed, SOME of those rows...have NON-ZERO values for both 'MIN_QTY' and 'MAX_QTY'.  Any thoughts here?  Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
It's not finding a match: what value do you want returned if no match is found?
...
SET t.MIN_QTY = ISNULL((
     select top (1) t2.MIN_QTY
     ...
    ), <put_desired_value_here_if_subquery_finds_no_value)
...
0
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 500 total points
Comment Utility
so here's what I was thinking would do what you're looking for:

update t1
   set t1.min_qty = 
        (
		     case
                  when t2.min_qty is null then t1.min_qty
                  when t2.min_qty = 0 then t1.min_qty
                  else t2.min_qty
			 end
		)
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = 2
  where
      t1.loc_id = 1;

Open in new window


Here is how I tested this out:

Created the table
create table dbo.im_inv_cell
(
	item_no varchar( 20 ) not null,
	loc_id varchar( 10 ) not null,
	dim_1_upr varchar( 15 ) not null,
	dim_2_upr varchar( 15 ) not null,
	dim_3_upr varchar( 15 ) not null,
	min_qty decimal( 15, 4 ) not null,
	max_qty decimal( 15, 4 ) not null
);

Open in new window


Put in some values representative of what I think you're trying to accomplish
insert into dbo.im_inv_cell
values
	( 'WOL-W03123', '1', '07.0', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '1', '07.0', 'W', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '1', '07.5', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '1', '07.5', 'W', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '1', '08.0', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '1', '08.0', 'W', '*', 2.0000, 4.0000 ),
	( 'WOL-W03123', '2', '07.0', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '2', '07.0', 'W', '*', 1.0000, 3.0000 ),
	( 'WOL-W03123', '2', '07.5', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '2', '07.5', 'W', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '2', '08.0', 'M', '*', 0.0000, 0.0000 ),
	( 'WOL-W03123', '2', '08.0', 'W', '*', 4.0000, 6.0000 );

Open in new window


check the contents of the table
select
	*
from
	dbo.im_inv_cell;

Open in new window


Check my update logic
   select 
		t1.item_no,
		t1.loc_id,
		t1.dim_1_upr,
		t1.dim_2_upr,
		t1.dim_3_upr,
        (
		     case
                  when t2.min_qty is null then t1.min_qty
                  when t2.min_qty = 0 then t1.min_qty
                  else t2.min_qty
			end
		) as min_qty,
		t1.max_qty
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = 2
  where
      t1.loc_id = 1;

Open in new window


Let 'er rip
update t1
   set t1.min_qty = 
        (
		     case
                  when t2.min_qty is null then t1.min_qty
                  when t2.min_qty = 0 then t1.min_qty
                  else t2.min_qty
			 end
		)
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = 2
  where
      t1.loc_id = 1;

Open in new window


Check new contents of table
select
	*
from
	dbo.im_inv_cell;

Open in new window

0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

OMG...*THANK YOU*!!!  This worked *PERFECTLY*!!!  No more errors.  I still don't know why MSSQL was reporting any error in the first place...so I'm guessing this had something to do...with the Query's syntax.  Anyhoo...again, thank you to ALL that replied...it's very much appreciated!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
There was something with the subquery where it wasn't returning any rows, which counts as a null and the not null constraint on the column didn't like that very much.  The query Kyle asked you to run was pretty much verification of that.  Glad it worked for you!
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

OOPS...I spoke to soon (sorry)...

I was so elated that there were no more errors...but when I checked the data...I saw...that actually "nothing" from Location-# 2 was updated from the data in Location-# 1.  Can you please take a quick looksee...and find out...what's wrong here?  I'm clueless as to 'why'... :-)

Thanks!...Mark
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
I wrote it the exact opposite, where location 2 was updating location 1.  This should do the reverse.

update t1
   set t1.min_qty = 
        (
		     case
                  when t2.min_qty is null then t1.min_qty
                  when t2.min_qty = 0 then t1.min_qty
                  else t2.min_qty
			 end
		)
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = 1
  where
      t1.loc_id = 2;

Open in new window


You'll need to get everything back to what it was prior to running this, however.  I really should have mentioned that you should always copy the contents of the table into a holding table before deploying changes.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

OK...but...I've got another quick 'gotcha'.  Suppose I *only* wanted to perform these data updates...WHEN...the value for 'MIN_QTY' was at LOCATION-# 2 was *not* equal to Zero.  I "thought" that if I'd altered your last line in your query to read:

   t1.loc_id = 2 and t1.min_qty<>0

...that this would work just fine...but, indeed, it doesn't.  It updates 'Zero' rows.  What am I missing and/or doing wrong?

Thanks!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
The not equal to zero part should be handled by the case statement up above =)  If you look at the case statement it has the part:

when t2.min_qty = 0 then t1.min_qty

Open in new window


So it basically checks to see if the min_qty is 0, and if it is, it sets t1.min_qty equal to t1.min_qty.  It also does that if t2.min_qty is null (meaning there were no rows that met the join criteria).
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

I think we're crossing our signals :-)

What I meant to do was...to avoid doing any updates, where the 'MIN_QTY' was not Zero...at Location-# 2.  There are a *bunch* of instances...where this IS valid 'MIN_QTY' values...already in place...at that location...and running your update query...will update ALL the Inventory location records to either "some" value (from Location-# 1)...or set them to Zero.  IF a non-zero value already exists in Location-# 2, then we don't want that value over-written.

I apologize if I wasn't clear in my descriptive text...sorry about that.  So...now...based on this info...how can we set the syntax on the Query...to accomplish this?  Please let me know...and THANKS!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
So I just want to try and make sure we're on the same page (I think I am seeing where I went awry).  You want to update min_qty at Location #2 only if it is 0.  If it is zero, you want to assign it the value from location 1, if it exists and has a non-zero value, is that correct?
0
 

Author Comment

by:datatechcorp
Comment Utility
YES!!!  :-)
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
I actually think you were going down the right path with your modification.  Try the following to see if you get anything back:

select 
		t1.item_no,
		t1.loc_id,
		t1.dim_1_upr,
		t1.dim_2_upr,
		t1.dim_3_upr,
        (
		     case
                  when t2.min_qty is null then t1.min_qty
                  when t2.min_qty = 0 then t1.min_qty
                  else t2.min_qty
			end
		) as min_qty,
		t1.max_qty
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = '1'
  where
      t1.loc_id = '2' and
	  t1.loc_id <> 0;

Open in new window


Take a look at the result set that that gives you and see if the data is what you're looking for.
0
 

Author Comment

by:datatechcorp
Comment Utility
...I think that the last statement in your select script should read "t1.min_qty<>0".  :-)

But, yes, based on my adjustment above, this *does* yield the correct result set.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Ugh.  Sorry.  I actually meant to make that t1.min_qty = '0'.  Since you said you only want to adjust the value if min_qty at loc_id 2 is 0.  I also decided to try and clean this up and get rid of the case statement to make it a little more straightforward (and put all the filtering in one place):

select 
		t1.item_no,
		t1.loc_id,
		t1.dim_1_upr,
		t1.dim_2_upr,
		t1.dim_3_upr,
		t2.min_qty,
		t1.max_qty
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = '1'
  where
      t1.loc_id = '2' and
	  t1.min_qty = 0 and
	  t2.min_qty <> 0 and
	  t2.item_no is not null;

Open in new window



This should give you *only* the rows that need to be updated.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

*PERFECT*!!!  *BRILLIANT*!!!  I tested the heck out of this with differing data scenarios, and it's (at least I hope :-) bulletproof!  Sorry, I was out of the office all morning at an appointment.  So, what should the Update Query look like then?  Please let me know...Thanks!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
This should do the trick:

update t1
set
  t1.min_qty = t2.min_qty
  from
      dbo.im_inv_cell as t1
           left join dbo.im_inv_cell as t2 on t1.item_no = t2.item_no and ( t1.dim_1_upr = t2.dim_1_upr and t1.dim_2_upr = t2.dim_2_upr and t1.dim_3_upr = t2.dim_3_upr ) and t2.loc_id = '1'
  where
      t1.loc_id = '2' and
	  t1.min_qty = 0 and
	  t2.min_qty <> 0 and
	  t2.item_no is not null;

Open in new window

0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

You 'da Man!!!  *PERFECT*!!!  Thank you SOOOoooooo much...I truly appreciate it!...Mark

Now that we've been better acquainted...I wonder...if you wouldn't mind...taking a peek at my other open question from a number of weeks ago.  Scott had chimed in...and then, it stalled.  Any thoughts on that one?  It can be found at:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28511758.html

Please let me know.  And, again, I'm so glad for all your efforts here. :-)  Mark
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now