Tabel A (PO, serial number)=& gt; This table PO is empty, need to fill in the corresponding data
(A0009)
(B0011)
Tabel B (PO, serail start, serial end, QTY)=& gt; Is corresponding to the table to have the PO number, each PO starting barcode, the end of the bar code, PO number
(010, A0001 A0010, 10)=& gt; PO for 010: the initiation of corresponding barcode is A0001, end of the bar code is A0010, the PO number of a total of 10
(020, B0001 B0100, 100)
SQL to how to write, will be two tables together, making the table A corresponding PO column update
(010, A0009)
(020, B0011)
Thank you very much
CodePudding user response:
The UPDATE TableAThe SET PO=TabelB. PO
The FROM TabelB
WHERE TableA. Serial number=TabelB. Serial number
CodePudding user response:
The composition of your barcode format is fixed, such as the prefix fixed was one of the first, is behind the Numbers, if is not fixed, it is more troublesome, but also determine the first digit from which,Below is a fixed prefix processing
IF OBJECT_ID (N 'TEMPDB for. DBO. # TA') IS NOT NULL
DROP TABLE # TA
GO
The CREATE TABLE # TA
(PO VARCHAR (10),
SERIAL_NUMBER VARCHAR (10))
INSERT INTO # TA
SELECT "', 'A0009' UNION ALL
SELECT "', 'B0011'
GO
IF OBJECT_ID (N 'TEMPDB for. DBO. # TB') IS NOT NULL
DROP TABLE # TB
GO
The CREATE TABLE # TB
(PO VARCHAR (10),
SERIAL_START VARCHAR (10),
SERIAL_END VARCHAR (10),
QTY INT)
INSERT INTO # TB
SELECT '010', 'A0001', 'A0010' UNION ALL 10
SELECT '020', 'B0001', 'B0100' 100
UPDATE # TA
The SET PO=p. O
The FROM
(SELECT *,
LEFT (SERIAL_NUMBER, 1) AS PRE_SERIAL,
CAST (SUBSTRING (SERIAL_NUMBER, 2, LEN (SERIAL_NUMBER) - 1) AS INT) AS SERIAL_NUMBER_NEW
The FROM # TA) A
The JOIN
(SELECT *,
LEFT (SERIAL_START, 1) AS PRE_SERIAL,
CAST (SUBSTRING (SERIAL_START, 2, LEN (SERIAL_START) - 1) AS INT) AS SERIAL_START_NEW,
CAST (SUBSTRING (SERIAL_END, 2, LEN (SERIAL_END) - 1) AS INT) AS SERIAL_END_NEW
The FROM # TB) AS B ON Amy polumbo RE_SERIAL=p. RE_SERIAL AND A.S ERIAL_NUMBER_NEW BETWEEN B.S ERIAL_START_NEW AND B.S ERIAL_END_NEW