need some help on a UPDATE function

#1

How do I change a table column to have NULL default values? This
doesn't work:

ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL

I am trying to write this function to update the error_flag=1 when
there is either a NULL or a ' ' in the inventory.ndc column. This is
the match field to the link.ndc column. I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.
Do you have to link to the table on every function in a php page or is
once enough?
I'm not getting this function to work as it is without the left
join?
The $link is the mysql_connect("server:,"user","pwd");
The rest of it should be something like AND there is no match in the
l.ndc table.
tia,

function setErrorFlag($link) {
mysql_select_db("cart_order",'$link');
"UPDATE cart_order.cart_inventory as i
//LEFT JOIN cart_order.link AS l ON i.ndc =l.ndc
SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
}

#2

In article <30da0552-b536-4eba-8017-ff372432c567 (AT) u17g2000prd (DOT) googlegroups.com>, JRough <jlrough (AT) yahoo (DOT) com> wrote:

Quote:

How do I change a table column to have NULL default values? This
doesn't work:

ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

You want to use ALTER COLUMN instead of MODIFY COLUMN, e.g.
ALTER TABLE cart_order ALTER COLUMN cart_inventory SET DEFAULT NULL;

Quote:

I am trying to write this function to update the error_flag=1 when
there is either a NULL or a ' ' in the inventory.ndc column. This is
the match field to the link.ndc column. I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.

Okay.....

Quote:

Do you have to link to the table on every function in a php page or is
once enough?

I have no idea what you mean by this.

Quote:

I'm not getting this function to work as it is without the left
join?

I don't know, are you? Why are you asking *us* if you're getting it to work?
We can't see what's happening...

Quote:

The $link is the mysql_connect("server:,"user","pwd");

Why do you imagine that is related in any way to whether your UPDATE statement
is correct?

Quote:

The rest of it should be something like AND there is no match in the
l.ndc table.

OK, so put some code in there that tests for a match. You mean the l.ndc
*column*, BTW.

Quote:

tia,

function setErrorFlag($link) {
mysql_select_db("cart_order",'$link');
"UPDATE cart_order.cart_inventory as i
//LEFT JOIN cart_order.link AS l ON i.ndc =l.ndc
SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
}

The LEFT JOIN has no effect here, even if it's not commented out, because the
WHERE clause doesn't test any conditions involving the second table. You need
to add such a condition.

#3

On Jun 19, 1:02*pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

In article <30da0552-b536-4eba-8017-ff372432c... (AT) u17g2000prd (DOT) googlegroups..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:

How do I change a table column to have NULL default values? *This
doesn't work:

ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

You want to use ALTER COLUMN instead of MODIFY COLUMN, e.g.
ALTER TABLE cart_order ALTER COLUMN cart_inventory SET DEFAULT NULL;

I am trying to write this function to update the error_flag=1 * when
there is either a NULL or a ' ' in the inventory.ndc *column. *This is
the match field to the link.ndc column. * I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.

Okay.....

Do you have to link to the table on every function in a php page or is
once enough?

I have no idea what you mean by this.

I'm not getting this function to work as it is without the left
join?

I don't know, are you? Why are you asking *us* if you're getting it to work?
We can't see what's happening...

The $link is the mysql_connect("server:,"user","pwd");

Why do you imagine that is related in any way to whether your UPDATE statement
is correct?

The rest of it should be something like AND there is no match in the
l.ndc table.

OK, so put some code in there that tests for a match. You mean the l.ndc
*column*, BTW.

tia,

function setErrorFlag($link) {
* * * *mysql_select_db("cart_order",'$link');
* * * *"UPDATE cart_order.cart_inventory as i
* * * *//LEFT JOIN cart_order.link AS l ON i.ndc =l.ndc
* * * *SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
* * * *}

The LEFT JOIN has no effect here, even if it's not commented out, becausethe
WHERE clause doesn't test any conditions involving the second table. You need
to add such a condition.
Okay, sorry about that I was asking a question relating to PHP and the

$link.
I don't know if you have to link to the server in each function in a
php page or once in the beginning is enough when you are doing other
sql queries?
By the way, the ALTER table didn't work because text fields can't be
changed to NULL so I only have to look for ' ' empty rows. All the
inventory columns are text because it is an upload from a csv file.

Regarding the left join condition. Would that be in a subquery where
the i.ndc != l.ndc
UPDATE cart_order.cart_inventory as i
SET i.error_flag= 1
WHERE i.ndc= ' '
(LEFT JOIN cart_order.link as l
WHERE i.ndc!=l.ndc)
tia,

#4

In article <2fa425db-d9d2-4e36-bb29-dce58171d822 (AT) e34g2000pra (DOT) googlegroups.com>, JRough <jlrough (AT) yahoo (DOT) com> wrote:

Quote:

On Jun 19, 1:02=A0pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
In article <30da0552-b536-4eba-8017-ff372432c... (AT) u17g2000prd (DOT) googlegroups=
..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:

How do I change a table column to have NULL default values? This
doesn't work:

ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

You want to use ALTER COLUMN instead of MODIFY COLUMN, e.g.
ALTER TABLE cart_order ALTER COLUMN cart_inventory SET DEFAULT NULL;

I am trying to write this function to update the error_flag=1 when
there is either a NULL or a ' ' in the inventory.ndc column. This is
the match field to the link.ndc column. I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.

Okay.....

Do you have to link to the table on every function in a php page or is
once enough?

I have no idea what you mean by this.

I'm not getting this function to work as it is without the left
join?

I don't know, are you? Why are you asking *us* if you're getting it to work?
We can't see what's happening...

The $link is the mysql_connect("server:,"user","pwd");

Why do you imagine that is related in any way to whether your UPDATE statement
is correct?

The rest of it should be something like AND there is no match in the
l.ndc table.

OK, so put some code in there that tests for a match. You mean the l.ndc
*column*, BTW.

tia,

function setErrorFlag($link) {
mysql_select_db("cart_order",'$link');
"UPDATE cart_order.cart_inventory as i
//LEFT JOIN cart_order.link AS l ON i.ndc = l.ndc
SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
}

The LEFT JOIN has no effect here, even if it's not commented out, because the
WHERE clause doesn't test any conditions involving the second table. You need
to add such a condition.
Okay, sorry about that I was asking a question relating to PHP and the
$link.
I don't know if you have to link to the server in each function in a
php page or once in the beginning is enough when you are doing other
sql queries?

That depends on how your functions are written. This is a separate issue,
however, and really should be addressed in a separate thread.

Quote:

By the way, the ALTER table didn't work because text fields can't be
changed to NULL so I only have to look for ' ' empty rows. All the
inventory columns are text because it is an upload from a csv file.

No, your ALTER TABLE command didn't work because you had the syntax wrong.
Didn't you read what I posted above?

Quote:

Regarding the left join condition. Would that be in a subquery where
the i.ndc != l.ndc
UPDATE cart_order.cart_inventory as i
SET i.error_flag=1
WHERE i.ndc= ' '
(LEFT JOIN cart_order.link as l
WHERE i.ndc!=l.ndc)

No. That's not syntactically valid.

You might try something like
WHERE i.ndc = ' ' OR i.ndc IN (SELECT blah-blah-blah)
Replace the blah-blah-blah inside the parentheses with an appropriate SELECT
statement to locate the condition(s) on which you want to match i.ndc to
l.ndc.

#5

Doug Miller wrote:

Quote:

In article <2fa425db-d9d2-4e36-bb29-dce58171d822 (AT) e34g2000pra (DOT) googlegroups.com>, JRough <jlrough (AT) yahoo (DOT) com> wrote:
On Jun 19, 1:02=A0pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
In article <30da0552-b536-4eba-8017-ff372432c... (AT) u17g2000prd (DOT) googlegroups=
..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:
How do I change a table column to have NULL default values? This
doesn't work:
ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

You want to use ALTER COLUMN instead of MODIFY COLUMN, e.g.
ALTER TABLE cart_order ALTER COLUMN cart_inventory SET DEFAULT NULL;

I am trying to write this function to update the error_flag=1 when
there is either a NULL or a ' ' in the inventory.ndc column. This is
the match field to the link.ndc column. I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.
Okay.....

Do you have to link to the table on every function in a php page or is
once enough?
I have no idea what you mean by this.

I'm not getting this function to work as it is without the left
join?
I don't know, are you? Why are you asking *us* if you're getting it to work?
We can't see what's happening...

The $link is the mysql_connect("server:,"user","pwd");
Why do you imagine that is related in any way to whether your UPDATE statement
is correct?

The rest of it should be something like AND there is no match in the
l.ndc table.
OK, so put some code in there that tests for a match. You mean the l.ndc
*column*, BTW.

tia,
function setErrorFlag($link) {
mysql_select_db("cart_order",'$link');
"UPDATE cart_order.cart_inventory as i
//LEFT JOIN cart_order.link AS l ON i.ndc = l.ndc
SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
}
The LEFT JOIN has no effect here, even if it's not commented out, because the
WHERE clause doesn't test any conditions involving the second table. You need
to add such a condition.
Okay, sorry about that I was asking a question relating to PHP and the
$link.
I don't know if you have to link to the server in each function in a
php page or once in the beginning is enough when you are doing other
sql queries?

That depends on how your functions are written. This is a separate issue,
however, and really should be addressed in a separate thread.

By the way, the ALTER table didn't work because text fields can't be
changed to NULL so I only have to look for ' ' empty rows. All the
inventory columns are text because it is an upload from a csv file.

No, your ALTER TABLE command didn't work because you had the syntax wrong.
Didn't you read what I posted above?
Regarding the left join condition. Would that be in a subquery where
the i.ndc != l.ndc
UPDATE cart_order.cart_inventory as i
SET i.error_flag=1
WHERE i.ndc= ' '
(LEFT JOIN cart_order.link as l
WHERE i.ndc!=l.ndc)

No. That's not syntactically valid.

You might try something like
WHERE i.ndc = ' ' OR i.ndc IN (SELECT blah-blah-blah)
Replace the blah-blah-blah inside the parentheses with an appropriate SELECT
statement to locate the condition(s) on which you want to match i.ndc to
l.ndc.

Don't get your hopes up, Doug. Look who you're talking to.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#6

On Jun 19, 7:31*pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

In article <2fa425db-d9d2-4e36-bb29-dce58171d... (AT) e34g2000pra (DOT) googlegroups..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:

On Jun 19, 1:02=A0pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
In article <30da0552-b536-4eba-8017-ff372432c... (AT) u17g2000prd (DOT) googlegroups=
..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:

How do I change a table column to have NULL default values? This
doesn't work:

ALTER TABLE cart_order modify column cart_inventory DEFAULT NULL

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

You want to use ALTER COLUMN instead of MODIFY COLUMN, e.g.
ALTER TABLE cart_order ALTER COLUMN cart_inventory SET DEFAULT NULL;

I am trying to write this function to update the error_flag=1 when
there is either a NULL or a ' ' in the inventory.ndc column. This is
the match field to the link.ndc column. I should also find all the
records where there IS an inventory.ndc value but there is no match in
the link table for example, if the i.ndc is wrong.

Okay.....

Do you have to link to the table on every function in a php page or is
once enough?

I have no idea what you mean by this.

I'm not getting this function to work as it is without the left
join?

I don't know, are you? Why are you asking *us* if you're getting it towork?
We can't see what's happening...

The $link is the mysql_connect("server:,"user","pwd");

Why do you imagine that is related in any way to whether your UPDATE statement
is correct?

The rest of it should be something like AND there is no match in the
l.ndc table.

OK, so put some code in there that tests for a match. You mean the l.ndc
*column*, BTW.

tia,

function setErrorFlag($link) {
* *mysql_select_db("cart_order",'$link');
* *"UPDATE cart_order.cart_inventory as i
* *//LEFT JOIN cart_order.link AS l ON i.ndc = l.ndc
* *SET i.error_flag=1 WHERE i.ndc =' ' OR i.ndc IS NULL";
* *}

The LEFT JOIN has no effect here, even if it's not commented out, because the
WHERE clause doesn't test any conditions involving the second table. You need
to add such a condition.
Okay, sorry about that I was asking a question relating to PHP and the
$link.
I don't know if you have to link to the server in each function in a
php page or once in the beginning is enough when you are doing other
sql queries?

That depends on how your functions are written. This is a separate issue,
however, and really should be addressed in a separate thread.

By the way, the ALTER table didn't work because text fields can't be
changed to NULL so I only have to look for ' ' empty rows. *All the
inventory columns are text because it is an upload from a csv file.

No, your ALTER TABLE command didn't work because you had the syntax wrong..
Didn't you read what I posted above?

Regarding the left join condition. *Would that be in a subquery where
the i.ndc != l.ndc
UPDATE cart_order.cart_inventory as i
SET i.error_flag=1
WHERE i.ndc= ' '
(LEFT JOIN cart_order.link as l
WHERE i.ndc!=l.ndc)

No. That's not syntactically valid.

You might try something like
WHERE i.ndc = ' ' OR i.ndc IN (SELECT blah-blah-blah)
Replace the blah-blah-blah inside the parentheses with an appropriate SELECT
statement to locate the condition(s) on which you want to match i.ndc to
l.ndc.

I have to try this on Monday but I only want to flag what is not a
match on the ndc in the two tables.
So if there is no inventory.ndc (i) then I know there is no match in
the link table. HOwever if there is a wrong
i.ndc then there won't be a match so I don't know what the subquery
SELECT would be.
There most likely won't be but I would like to trap for it as it would
be an erro. I guess it would be
WHERE i.ndc = ' ' OR NOT IN(SELECT l.ndc from link as l)
Something like that?
I really thank you for your answer.

#7

In article <323ca44d-699f-4ef3-b961-6e06e38e6057 (AT) e34g2000pra (DOT) googlegroups.com>, JRough <jlrough (AT) yahoo (DOT) com> wrote:
[...]

Quote:

I have to try this on Monday but I only want to flag what is not a
match on the ndc in the two tables.
So if there is no inventory.ndc (i) then I know there is no match in
the link table. HOwever if there is a wrong
i.ndc then there won't be a match so I don't know what the subquery
SELECT would be.
There most likely won't be but I would like to trap for it as it would
be an erro. I guess it would be
WHERE i.ndc = ' ' OR NOT IN(SELECT l.ndc from link as l)
Something like that?

Something very close to that, actually -- you have the syntax almost, but not
quite, correct.

WHERE i.ndc = ' ' OR i.ndc NOT IN (SELECT l.ndc FROM link AS l)

Quote:

I really thank you for your answer.

You're welcome. I hope this helps.

#8

On Jun 20, 4:21*am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

In article <323ca44d-699f-4ef3-b961-6e06e38e6... (AT) e34g2000pra (DOT) googlegroups..com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:
[...]

I have to try this on Monday but I only want to flag what is not a
match on the ndc in the two tables.
So if there is no inventory.ndc (i) then I know there is no match in
the link table. *HOwever if there is a wrong
i.ndc then there won't be a match so I don't know what the subquery
SELECT would be.
There most likely won't be but I would like to trap for it as it would
be an erro. *I guess it would be
WHERE *i.ndc = ' ' OR NOT *IN(SELECT l.ndc from link as l)
Something like that?

Something very close to that, actually -- you have the syntax almost, butnot
quite, correct.

WHERE i.ndc = ' ' OR i.ndc NOT IN (SELECT l.ndc FROM link AS l)

I really thank you for your answer.

You're welcome. I hope this helps.

I'm afraid it won't help. Just take a look at her previous posting
going back over many years. Despite many people trying to help, she
still shows a total inability to understand anything.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

need some help on a UPDATE function

comp.databases.mysql comp.databases.mysql

need some help on a UPDATE function - 06-19-2010 , 02:43 PM

Re: need some help on a UPDATE function - 06-19-2010 , 03:02 PM

Re: need some help on a UPDATE function - 06-19-2010 , 03:37 PM

Re: need some help on a UPDATE function - 06-19-2010 , 09:31 PM

Re: need some help on a UPDATE function - 06-19-2010 , 09:41 PM

Re: need some help on a UPDATE function - 06-19-2010 , 10:14 PM

Re: need some help on a UPDATE function - 06-19-2010 , 10:21 PM

Re: need some help on a UPDATE function - 06-20-2010 , 06:14 AM