Re: Update query question

[ViSolve DB Team]

Hi,,

The Update query of yours will do fine..
otherwise try using string functions [instr()] like

mysql> update inventory_items set name='necklace' where 
instr(description,'necklace')>0;


Thanks
ViSolve DB Team.
- Original Message - 
From: "Jerry Jones" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, February 03, 2007 8:42 AM
Subject: Update query question



I am new to mysql. I am trying to do a simple update query to update a
field based on the contents of another field in the same table.
Here is what I have.
update inventory_items set name = "necklace" where description like
"%necklace%";
I am not sure what is wrong. select * from inventory_items where
description like "%necklace%"; works just fine. I cannot find much
online to help me out with this.
To summarize, I need to update the name field to "necklace" when the
word necklace shows up anywhere in the description field.
Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query question

[Jerry Jones]

I am new to mysql. I am trying to do a simple update query to update a
field based on the contents of another field in the same table.
Here is what I have.
update inventory_items set name = "necklace" where description like
"%necklace%";
I am not sure what is wrong. select * from inventory_items where
description like "%necklace%"; works just fine. I cannot find much
online to help me out with this.
To summarize, I need to update the name field to "necklace" when the
word necklace shows up anywhere in the description field.
Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: update query question

[Chris W. Parker]

[EMAIL PROTECTED] 
on Wednesday, July 07, 2004 11:08 AM said:

> Have you tried this other way of making an inner join?

no i did not because i did know you could do a JOIN on an UPDATE. thanks
for your suggestions i will try them out.


chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query question

[SGreen]

Have you tried this other way of making an inner join?

UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.id

But that does not seem right our you could say:

UPDATE products_categories AS pc
SET pc.prod_sequential_id = pc.prod_id

and have the same statement. I think this is what you meant to say:

UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.sequential_id

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


|-+>
| |   "Chris W. Parker"|
| |   <[EMAIL PROTECTED]|
| |   .com>|
| ||
| |   07/06/2004 01:14 |
| |   PM   |
| ||
|-+>
  
>|
  |
|
  |   To:   <[EMAIL PROTECTED]>
|
  |   cc:  
|
  |   Fax to:  
        |
  |   Subject:  update query question  
|
  
>|




hello,

i've had to change some of the tables in my db to accomodate some
greater flexibility in the application that uses it and because of this
i need to go through and update all the records. i've done one table by
hand and it had about 100 records and took about 20 minutes. but this
next table has about 550 records and i really don't feel like doing this
all by hand. i'm using MySQL Control Center to do this editing so i'd
like to know if there's a single SQL statement i could use to update all
the rows.

here is a simple representation:

products: (pay no attention to the poor choice in column names. this is
a retrofitting and will be fixed in later versions.)
+--+---+
| id   | sequential_id |
+--+---+
| PRDX-41  | 1 |
| ABCX-01  | 2 |
| FF00-11  | 3 |
\/\/\/\/\/\/\/\/
| ETC0-99  |   500 |
+--+---+

the 'prod_sequential_id' column was added later to the
products_categories table.

products_categories:
+-+-+++
| id  | prod_id | prod_sequential_id | cat_id |
+-+-+++
|   1 | PRDX-41 |  0 | 41 |
|   2 | PRDX-41 |  0 | 15 |
|   3 | ABCX-01 |  0 | 13 |
|   4 | FF00-11 |  0 | 89 |
\/\/\/\/\/\/\/\/
| 610 | ETC0-99 |  0 | 41 |
+-+-+++

so... as you can see, prod_sequential_id has all 0's in its column. it
should contain the value of products.sequential_id WHERE
products_categories.prod_id = products.id.

the problem is that i'm not sure how to do this all in one statement (or
if it's even possible):

(i know the following does not work, but it's basically the logic i
think i need.)

UPDATE products_categories AS pc, products AS p
SET pc.prod_sequential_id = p.id
WHERE pc.prod_id = p.id;


thanks for your help.
chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update query question

[Chris W. Parker]

hello,

i've had to change some of the tables in my db to accomodate some
greater flexibility in the application that uses it and because of this
i need to go through and update all the records. i've done one table by
hand and it had about 100 records and took about 20 minutes. but this
next table has about 550 records and i really don't feel like doing this
all by hand. i'm using MySQL Control Center to do this editing so i'd
like to know if there's a single SQL statement i could use to update all
the rows.

here is a simple representation:

products: (pay no attention to the poor choice in column names. this is
a retrofitting and will be fixed in later versions.)
+--+---+
| id   | sequential_id |
+--+---+
| PRDX-41  | 1 |
| ABCX-01  | 2 |
| FF00-11  | 3 |
\/\/\/\/\/\/\/\/
| ETC0-99  |   500 |
+--+---+

the 'prod_sequential_id' column was added later to the
products_categories table.

products_categories:
+-+-+++
| id  | prod_id | prod_sequential_id | cat_id |
+-+-+++
|   1 | PRDX-41 |  0 | 41 |
|   2 | PRDX-41 |  0 | 15 |
|   3 | ABCX-01 |  0 | 13 |
|   4 | FF00-11 |  0 | 89 |
\/\/\/\/\/\/\/\/
| 610 | ETC0-99 |  0 | 41 |
+-+-+++

so... as you can see, prod_sequential_id has all 0's in its column. it
should contain the value of products.sequential_id WHERE
products_categories.prod_id = products.id.

the problem is that i'm not sure how to do this all in one statement (or
if it's even possible):

(i know the following does not work, but it's basically the logic i
think i need.)

UPDATE products_categories AS pc, products AS p
SET pc.prod_sequential_id = p.id
WHERE pc.prod_id = p.id;


thanks for your help.
chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]