SELECT and UPDATE on 2 tables - 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

Transact-SQL (2000)

SELECT and UPDATE on 2 tables

Author

Topic

mickyjtwin
Starting Member

12 Posts

Posted - 2007-02-27 : 19:10:45

I have the following 2 tables:

tblTable1
-------------
Name varchar(128)
Code1 varchar(16)
Code2 varchar(16)
VOC bit

tblTable2
------------
Name varchar(128)
Code varchar(16)

Basically table 2 has names that are in table1, that if match, will change the VOC value to 1.

I would like it to run on Code, where tblTable2.Code = tblTable1.Code1 + tblTable1.Code2

so if the field value of Code in table2 matches the Code1+Code2 value in table2 set VOC to 1

I'm not sure how to get this started with SELECTS, as I'm essentiallyl running through 2 sets of fields at the one time?

Thanks,

Mick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 19:19:25

[code]
update t1
set VOC = 1
from tblTable1 t1 inner join tblTable2 t2
on t1.name = t2.name
and t1.Code1 + t1.Code2 = t2.Code
[/code]

mickyjtwin
Starting Member

12 Posts

Posted - 2007-02-27 : 19:28:01

that's awesome, thankyou, my T-SQL is very limited, but I'm learning heaps!! how would that change if i then wanted to keep that update, but if it couldn't find the code (it would assume that it's a new record from t2) it would insert the information from t2 into t1?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 21:25:44

[code]
declare @x int

select @x = ? -- where to split code to code 1 and code 2
insert into tblTable1 (Name, Code1, Code2)
select Name, left(Code, @x), right(Code, len(Code) - @x)
from tblTable2 t2
where not exists (select * from tblTable1 x where x.Name = t2.Name and x.Code1 + x.Code2 = t2.Code)
[/code]

mickyjtwin
Starting Member

12 Posts

Posted - 2007-02-28 : 17:59:22

ok, now to extend this even further!!

tblTable1 also has an objectID column, that is contrained to another table called tblObject. Normally, a new ID is created in tblObjectID first along with a description, then that objectID that is returned, is used to insert into tblTable1. By using the previous insert example, the logic would be...

declare @x int

insert into tblTable1(("insert into tblObjectID, description, and return newly created objectID), Code1, Code2)
select Name, left(Code, @x), right(Code, len(Code - @x)
from tblTable2 t2
where not exists (select * from tblTable1 x where x.name = t2.Name and x.Code1 + x.Code2 = t2.Code)

I hope this makes sense what I mean.

Thanks,
Mick

mickyjtwin
Starting Member

12 Posts

Posted - 2007-02-28 : 23:54:14

For those interested, i found a solution, and this is the final script i went with:

DECLARE @Code1 char(3), @Code2 char(3), @name varchar(50), @lvoc bit

/* declare cursor and select statement of values to insert */
DECLARE @get_objectID CURSOR
SET @get_objectID = CURSOR FOR
SELECT LEFT(Code, 3), SUBSTRING(Code, 3, Len(Shade) - 3), name, '1'
FROM Table2 au
WHERE NOT EXISTS (SELECT * FROM Table1 ca WHERE ca.Code1 + ca.Code2 = au.Code AND ca.name = au.name)

OPEN @get_objectID

/* get first record and place into variables for insert */
FETCH NEXT FROM @get_objectID INTO @Code1, @Code2, @name, @lvoc

/* while record found, insert data */
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @objectID UNIQUEIDENTIFIER
SET @objectID = NEWID()
/* insert new object into cms_object, and retrieve new object_id */
INSERT INTO Table3 ([object_id], ts_created, ts_modified, description) VALUES(@objectID, GetDate(), GetDate(), 'description')

INSERT INTO Table1 ([object_id], Code1, Code2, name, lvoc) VALUES (@objectID, @Code1, @Code2, @name, @lvoc)

/* get next record */
FETCH NEXT FROM @get_objectID INTO @Code1, @Code2, @name, @lvoc
END

CLOSE @get_objectID
DEALLOCATE @get_objectID

Works a dream!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources