SQL to update a table...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

Brad Pears

How would the SQL statement be coded for the following...

I want to update the "Code" column in table2 (shown below) to the ID value
found in table1 where field "optcode" in table 2 matches field optcode in
table 1...
(then I will eliminate the optcode field altogether from my db design)

Here is the example....

Table 1 (master)
ID OptCode
1 test
2 test2
3 test3

Table2 (Options)
ID OptCode Code
1 test
2 test2
3 test3
4 test3


Result after appropriate SQL update runs...

Table2
ID OptCode Code
1 test 1
2 test2 2
3 test3 3
4 test3 3

Thanks!

Brad
 
I'm not sure it makes sense to eliminate Table1, and to store the value in
Table2 would be redundant (and therefore not a good idea).
 
How would the SQL statement be coded for the following...

I want to update the "Code" column in table2 (shown below) to the ID value
found in table1 where field "optcode" in table 2 matches field optcode in
table 1...
(then I will eliminate the optcode field altogether from my db design)

Here is the example....

Table 1 (master)
ID OptCode
1 test
2 test2
3 test3

Table2 (Options)
ID OptCode Code
1 test
2 test2
3 test3
4 test3


Result after appropriate SQL update runs...

Table2
ID OptCode Code
1 test 1
2 test2 2
3 test3 3
4 test3 3
Click to expand...

Is there a unique index on OptCode in Table1? If so, it is fairly easy
to do such an update. However, if there is no unique index, first you
need to examine Table1 for duplicate values of OptCode having
different ID's.
 
No I won't be eliminating TABLE1 - just the column entitled "optcode"
because it is redundant. Table2 contains rows which indicate table1 items
that are options for other items in table1 - kind of a reciprocal
relationship.

Brad
 
No there is not a unique index on optcode unfortunately. Many of the rows in
table1 do not have an optcode at all - hence they are all null values -
hence duplicate values.

I guess I'll have to write some code to do the job then?

Brad

How would the SQL statement be coded for the following...

I want to update the "Code" column in table2 (shown below) to the ID value
found in table1 where field "optcode" in table 2 matches field optcode in
table 1...
(then I will eliminate the optcode field altogether from my db design)

Here is the example....

Table 1 (master)
ID OptCode
1 test
2 test2
3 test3

Table2 (Options)
ID OptCode Code
1 test
2 test2
3 test3
4 test3


Result after appropriate SQL update runs...

Table2
ID OptCode Code
1 test 1
2 test2 2
3 test3 3
4 test3 3
Click to expand...

Is there a unique index on OptCode in Table1? If so, it is fairly easy
to do such an update. However, if there is no unique index, first you
need to examine Table1 for duplicate values of OptCode having
different ID's.
 
No there is not a unique index on optcode unfortunately. Many of the rows in
table1 do not have an optcode at all - hence they are all null values -
hence duplicate values.

I guess I'll have to write some code to do the job then?
Click to expand...

Not necessarily ... presumably, you won't have a related record in
Table2 if there is no entry for OptCode in Table1. You can just add a
"WHERE Table1.OptCode Is Not Null" criteria in the SQL update
statement.

A column can still have a unique index in spite of null values; this
is not the case for a primary key column, though.
 
Many of the rows in
table1 do not have an optcode at all - hence they are all null values -
hence duplicate values.
Click to expand...

Just as a side thought: if X is null, and Y is null, what is the value
of the statement: "X = Y" ?

(assuming that "=" is a comparison operator and not the assignment
operator here, as in: "If X = Y Then ..."; i.e. it should return
either true or false.)
 
I tried to create a unique index on table1.optcode and it will not let me do
that. The error indicates there was a duplicate key found (which is the null
values)


PS... In access it appears you can do this but in SQL server 2000 (which is
what I am converting to from Access) it appears you shoul dbe able to ignore
duplicate values but it is not working....

Brad
No there is not a unique index on optcode unfortunately. Many of the rows
in
table1 do not have an optcode at all - hence they are all null values -
hence duplicate values.

I guess I'll have to write some code to do the job then?
Click to expand...

Not necessarily ... presumably, you won't have a related record in
Table2 if there is no entry for OptCode in Table1. You can just add a
"WHERE Table1.OptCode Is Not Null" criteria in the SQL update
statement.

A column can still have a unique index in spite of null values; this
is not the case for a primary key column, though.
 
I tried to create a unique index on table1.optcode and it will not let me do
that. The error indicates there was a duplicate key found (which is the null
values)
Click to expand...

Do you have empty strings in those rows, or nulls? There is a big
difference. A unique index will allow multiple null values because two
or more rows with a column containing null isn't the same thing as
having duplicate values; the reason being that null isn't a value (see
my other post, for example).
 
Null = Null returns Null.

If you want X = Y to return True when they're both Null, use the Nz
function:

Nz(X, 0) = Nz(Y, 0) if X and Y are numeric fields (or dates or boolean),
Nz(X, "") = Nz(Y, "") if they're text.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Many of the rows in
table1 do not have an optcode at all - hence they are all null values -
hence duplicate values.
Click to expand...

Just as a side thought: if X is null, and Y is null, what is the value
of the statement: "X = Y" ?

(assuming that "=" is a comparison operator and not the assignment
operator here, as in: "If X = Y Then ..."; i.e. it should return
either true or false.)
 
Null = Null returns Null.
Click to expand...

Right! (at least as far as MSAccess is concerned).

Well, actually in other databases, it might also return false
depending on the implementation. But it will never return true, as I
was trying to point out to the OP (propagation of null). AFAIK the
equality comparison should not ever return null; it should actually
return only true or false.

The point I was trying to make is that you only have duplicate records
if their identifiers compare equal; obviously, that can never be the
case with nulls.
 
The point I was trying to make is that you only have duplicate records
if their identifiers compare equal; obviously, that can never be the
case with nulls.
Click to expand...


Unless you use Nz, as I illustrated.
 
Back
Top