November 3, 2008 at 6:53 am
Hi,
I have a little question. I have two tables
table1
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT
obj_num INT NOT NULL,
brand CHAR (16)¨
table2, this is temporary
brand CHAR (16)¨
I want to move data from the brand column in table2 into table1. This below doesn´t work because i don´t have the column obj_num in table2. So when i try to insert data a get error message "obj_num can not be NULL"
INSERT INTO table1 AS t1 (t1.brand)
SELECT DISTINCT t2.brand
FROM table2 as t2
Can a generate a loop that puts in numbers in table1.obj_num in the insert statement i wrote ? And if this is possible, can i choose wish number the obj_num should start on ?
Or how do I solve this problem ?
Regards
Magnus
November 3, 2008 at 7:02 am
you need to change the isnsert so that it has your objnum in it, typcially by joining to the right table.
for example, if you have a lookup table that has the "brand" related to the obj_num:
INSERT INTO table1 AS t1 (t1.obj_num.t1.brand)
SELECT DISTINCT t3.obj_num,t2.brand
FROM table2 as t2
INNER JOIN TblObjects AS t2 ON t2.brand = t3.brand
other than that, you could try entering a default value, like zerobut you'll have to clean up the right values later?:
INSERT INTO table1 AS t1 (t1.obj_num.t1.brand)
SELECT DISTINCT 0, t2.brand
FROM table2 as t2
Lowell
November 4, 2008 at 3:36 am
This solves the problem but it´s not quite what I want. The AUTOINCREMENT counts and thats good, but I want to modify the obj_num (AUTOINCREMENT) in table2 when I´m reading in the brand column.
create table custom.table2 (
obj_num INT NOT NULL DEFAULT AUTOINCREMENT,
brand CHAR (16)
)
//
go
//
INSERT INTO custom.table2 (table2.brand)
SELECT DISTINCT table1.brand
FROM custom.table1
WHERE table1.brand IS NOT NULL
The result looks like this now in table2. This is just an example.
obj_num brand
1 ** Shoes ** (Header)
2 bla bla
3 bla bla
4 ** T-Shirt ** (Header)
5 bla bla
6 bla bla
The result i´m looking for is something like this.
obj_num brand
10000 ** Shoes ** (Header)
10001 bla bla
10002 bla bla
20000 ** T-Shirt ** (Header)
20001 bla bla
20002 bla bla
So I think I´m looking for some loop or if statement that checks when you put in the "brand" in the table2. First of all, that the AUTOINCREMENT starts counting at 10000. When you are reading from the table1 to table2 you make an check and looks for **, for every ** that you find you jumps down a block and counting put in values.
Hopefully you understand what I´m looking for 🙂 🙂
Regards
Magnus
November 4, 2008 at 7:00 am
So basically i´m looking for some function that generates numbers into the obj_num column depending of what values you get from the brand column. The must be some way to modify the obj_num column either before you put in the brand values or afterwords. so i taught you could modify autoincrement.
Maybe first just create the brand column och put in the values from table1.brand, and then use the ALTER TABLE function and add a column called obj_num and then put in numbers in depending of what values you have in the brand column ?
/Magnus
November 4, 2008 at 7:12 am
i don't really like the structure of your table; you are really assuming that you will be inserting the header "T shirts" at the same time as all it's details; in reality, that is far from the truth, as you must realize a new item will be added later, right? and then it is up to some kind of user input to associate the latest tshirt or shoes to the proper header.
If it were me, i'd have a header/category table, and a seperate detail table. add a shoes/tshirt header, and the detail table would have a foreign key to the Header/Category....that way you could add more items on demand without the issue. This is typical in almost every shopping cart/inventory type database....
that also gets rid of the coding bandaid where you "assume" everything starting with 200** is associated with Category 20000; instead you have a real, enforced foreign key which can be easily queried to determine what item is associated to what category.
Can you redo your structure at this point, or are you stuck with what you inherited?
Lowell
November 4, 2008 at 7:28 am
as an example, I'd forget trying to restart the object number as restarting at 1 with a category break, and just generate a number based on Category and item
to get data like this:
CATID CATNAME ITEMID ITEMNAME OBJ_NUM
1 Shoes 1 Reeboks 10001
1 Shoes 2 Adidas 10002
1 Shoes 3 Sketchers 10003
2 TShirts 4 Hanes 20004
2 TShirts 5 Fruit Of The Loom 20005
2 TShirts 6 China Shirts 20006
here's a sample structure:
[font="Courier New"]
CREATE TABLE CATEGORY (
CATID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CATNAME VARCHAR(100) )
CREATE TABLE ITEMS(
ITEMID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CATID INT REFERENCES CATEGORY(CATID),
ITEMNAME VARCHAR(100) )
DECLARE @headerKey INT
INSERT INTO CATEGORY(CATNAME) VALUES('Shoes')
SELECT @headerKey=SCOPE_IDENTITY()
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'Reeboks')
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'Adidas')
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'Sketchers')
INSERT INTO CATEGORY(CATNAME) VALUES('TShirts')
SELECT @headerKey=SCOPE_IDENTITY()
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'Hanes')
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'Fruit Of The Loom')
INSERT INTO ITEMS(CATID,ITEMNAME) VALUES(@headerKey,'China Shirts')
CREATE VIEW MyCatalog
AS
SELECT
CATEGORY.CATID,
CATEGORY.CATNAME,
ITEMS.ITEMID,
ITEMS.ITEMNAME,
CATEGORY.CATID * 10000 + ITEMS.ITEMID AS OBJ_NUM
FROM CATEGORY
INNER JOIN ITEMS ON CATEGORY.CATID = ITEMS.CATID[/font]
Lowell
November 4, 2008 at 10:01 am
Lowell:
I´ll get back to later today, thx for the post 🙂
/Magnus
November 6, 2008 at 3:30 am
Lowell or anyone: 🙂 See my attached files, I have put in some screen shots and writing what i´m trying to accomplish.
Regards
Magnus
November 17, 2008 at 5:16 am
Now i want to update one table depending on values in a other table.
Or there is three tables involved. Tables below.
table1
****
mi_seq
obj_num
mgrp
table2
*****
mi_seq
obj_num
maj_grp_seq
table3
*****
maj_grp_seq
name
I want to update table2.maj_grp_seq with the table3.maj_grp_seq where table1.mgrp = table3.name.
Regards
Magnus
November 17, 2008 at 5:26 am
your psuedocode is pretty much the SQL;
update table2.maj_grp_seq with the table3.maj_grp_seq where table1.mgrp = table3.name.
only thing you are missing is how the data is related to table2; since they have two common column, I think my assumption on the WHERE clause is pretty close:
update table2
SET table2.maj_grp_seq = table3.maj_grp_seq
FROM table3
INNER JOIN Table1 ON table3.name table1.mgrp
WHERE table2.mi_seq = table1.mi_seq
AND table2.obj_num = table1.obj_num
m.berggren (11/17/2008)
Now i want to update one table depending on values in a other table.Or there is three tables involved. Tables below.
table1
****
mi_seq
obj_num
mgrp
table2
*****
mi_seq
obj_num
maj_grp_seq
table3
*****
maj_grp_seq
name
I want to update table2.maj_grp_seq with the table3.maj_grp_seq where table1.mgrp = table3.name.
Regards
Magnus
Lowell
November 17, 2008 at 5:59 am
Lowell: Thx a lot! It worked perfekt! 😀
Regards
Magnus
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply