Update table without using column name 1

[trenttc]

I have a table (TABLE1) that is updated by a second table (TABLE2) based on a field that is common to both tables (CODE#). For each CODE# match, there are from one to three TABLE1 fields (TYPE1,TYPE2,TYPE3) updated with data from a single TABLE2 field (GROUP). On a match, the first GROUP value must go in TYPE1, the second in TYPE2, etc. So how do I SET the reference of TABLE1.TYPE(1-3) to TABLE2.GROUP?

TABLE1
CODE# TYPE1 TYPE2 TYPE3
1234

TABLE2
CODE# GROUP
1234 4
1234 2
1234 7

Here's what I have so far:
UPDATE TABLE1 SET
TABLE1.TYPE1 = TABLE2.GROUP ???
TABLE1.TYPE2 = TABLE2.GROUP ???
TABLE1.TYPE3 = TABLE2.GROUP ???
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.CODE# = TABLE2.CODE#

Thanks in advance.

 

[tlbroadbent]

There are several solutions. Here is one. Hopefully, it will work in your RDMS.

UPDATE TABLE1 SET
TYPE1 =
Case When q.RecCnt=1
Then q.Group Else t1.Type1 End,
TYPE2 =
Case When q.RecCnt=2
Then q.Group Else t1.Type2 End,
TYPE3 =
Case When q.RecCnt=3
Then q.Group Else t1.Type3 End

FROM TABLE1 t1

INNER JOIN

(SELECT Code#,
(Select Count(*) TABLE2
Where Code#=t2.Code#
And Group<=t2.Group) As RecNo
From table2 t2) As q

ON t1.CODE# = q.CODE# Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

I'm using Sybase Adaptive Server Anywhere 7.0. Regarding the solution posted, it complains &quot;no column Group found&quot;
And Group<=t2.Group) As RecCnt (RecNo)
Any ideas???

 

[tlbroadbent]

Wow! Talk about errors. Loks like you already caught 1 of them. Here is a corrected query.

UPDATE TABLE1 SET
TYPE1 =
Case When q.RecNo=1
Then q.Group Else t1.Type1 End,
TYPE2 =
Case When q.RecNo=2
Then q.Group Else t1.Type2 End,
TYPE3 =
Case When q.RecNo=3
Then q.Group Else t1.Type3 End

FROM TABLE1 t1

INNER JOIN

(SELECT Code#, Group,
(Select Count(*) TABLE2
Where Code#=t2.Code#
And Group<=t2.Group) As RecNo
From table2 t2) As q

ON t1.CODE# = q.CODE# Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

To tlbroadbent
One more question. I didn't realize it before but I need to sort table2 before the updating occurs. Where do I fit the subquery into the whole?
SELECT * FROM TABLE2
ORDER BY CODE#

 

[tlbroadbent]

I don't understand the need to sort on an update. Please explain. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

To tlbroadbent
Table2 initially To do the update of Table1
looks like this: it should look like this:
Code# Group Code# Group
1234 4 1234 4
2468 6 1234 2
1234 2 1234 7
2468 1 2468 6
1234 7 2468 1

A sort on code# before the update will put the group values together. Maybe I should just sort Table2 in a separate query before running the update query?
FYI - I spent alot of time checking the posts looking for an example of my particular problem. I hope the title I gave it will help others find your valuable solution. Your help is much appreciated.

 

[tlbroadbent]

1) By definition relational tables are not ordered.
2) Ordering a table before updating will not change the outcome of the update in this example.
3) You can order a result set of a SELECT statement by CODE# but the order of the GROUP column is not guaranteed unless you also order by GROUP.
4) The UPDATE query that I suggested will actually place the GROUP in ascending order across the row in table1. If that is a problem, then we'll have to examine other possible methods to update table1. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tlbroadbent]

By the way, I recommend posting Sybase questions in the Sybase forum (forum187). There may be extensions in Sybase that will help you accomplish what you want. The Sybase ordering default may be different than the ANSI standard. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

to tlbroadbent

When I run the Update, all three Type fields get the same group value. The counter seems to be stuck at 1?

 

[tlbroadbent]

Could you post your query? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

To tlbroadbent
Here is the actual query. I said all three type fields get the same group value. That's not true. What happens is that Type1 gets updated (correctly) and type2 and type3 are left blank (when they should have values).
You are a patient man, tlbroadbent

UPDATE NSM_PRODUCTS SET
TYPE1 =
CASE WHEN Q.RECNO=1
THEN Q.REPGROUP ELSE T1.TYPE1 END,
TYPE2 =
CASE WHEN Q.RECNO=2
THEN Q.REPGROUP ELSE T1.TYPE2 END,
TYPE3 =
CASE WHEN Q.RECNO=3
THEN Q.REPGROUP ELSE T1.TYPE3 END

FROM NSM_PRODUCTS T1
INNER JOIN
(SELECT PROD_CODE, REPGROUP,
(SELECT COUNT(*) PROD_RG
WHERE PROD_CODE = T2.PROD_CODE
AND REPGROUP <= T2.REPGROUP) AS RECNO
FROM PROD_RG T2) AS Q

ON T1.CODE = Q.PROD_CODE;

 

[tlbroadbent]

I notice that the keyword FROM is missing in the sub-query that creates RecNo. The query shouldn't even run with that syntax error so let me know if it is actually missing in your query or only missing on the post.

FROM NSM_PRODUCTS T1
INNER JOIN
(SELECT PROD_CODE, REPGROUP,
(SELECT COUNT(*) FROM PROD_RG
WHERE PROD_CODE = T2.PROD_CODE
AND REPGROUP <= T2.REPGROUP) AS RECNO
FROM PROD_RG T2) AS Q

ON T1.CODE = Q.PROD_CODE;
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[trenttc]

to tlbroadbent
My apologies. This is an append to my previous post.
The Type1 field gets updated, but incorrectly. Type1 gets the Group value that should be in the Type3 field. Type2 and Type3 are still blank.

 

[trenttc]

to tobroadbent

You are a genius! It actually was missing the FROM but ran anyway (incorrectly). Adding the FROM fixed it.