2

I am using Microsoft SQL Server 2005

I have two tables, lets say TableA, and TableB

CREATE TABLE TableA (
A_ItemNumber (int)
A_IsDeleted  (bit)
)

CREATE TABLE TableB (
B_ItemNumber (int)
B_OrderNumber (varchar)
)

INSERT INTO TableB VALUES 
(1, 'XY004005'),
(2, 'XY005125'),
(3, 'XY499999'),
(4, 'XY511340')

I need to write an update query that does the following:

  1. Gets the B_ItemNumber for ALL B_OrderNumbers where the int part of the string < 500000 (XY004005 would be included since 004005 < 500000, but XY511340 would not)

  2. Use this item number to UPDATE A_IsDeleted WHERE A_ItemNumber = B_ItemNumber

I basically want to change A_IsDeleted to TRUE for all A_ItemNumber = B_ItemNumber where B_OrderNumber < XY500000

I thought this could be a simple join/update but it doesn't look like that is allowed.

gofr1
  • 15,741
  • 11
  • 42
  • 52
Eddie
  • 179
  • 2
  • 14
  • I did see that answer. I'll read it over again. I also have strings where there should be ints thanks to whoever designed this DB so I'll dig a bit more and see what I can come up with – Eddie Jul 17 '14 at 18:14

1 Answers1

2

Pretty sparse on details but something like this should get you close.

Update A
set IsDeleted = 1
from TableA A
join TableB B on b.ItemNumber = A.ItemNumber
where b.OrderNumber > 'XY004005'
and b.OrderNumber < 'XY500000'
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Ok, so i can just do a > even though it is a string? I thought I would need to get a substring somewhere and maybe convert string to int. But XY does precede every OrderNumber so maybe this will work – Eddie Jul 17 '14 at 18:12
  • Sure. It is just sorting the values as varchar and not ints. This comes with it's own challenges but for your case it should work fine. This will work on the same concept as ordering people by LastName. I have to ask...if every order starts with "XY" what is the point? Why not remove that and change the datatype to an int. – Sean Lange Jul 17 '14 at 18:18