Update Query Where Values Will Come From Other Table

Update A Table With Values In Another
I have the following table

tableA
column_a
column_x
column_y
column_z

tableB
column_x
column_y
column_z

How do I update the rows of tableA that match rows of tableB (all rows
of tableB are unique)

What I want to acheive is a check against tableA.column_x with
tableB.column_x, if they match, then update the row in tableA.column_y
with the value of tableB.column_y and tableA.column_z with
tableB.column_z

---

Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field.

I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:

---

Update One Table Value With Values From Another Table
I am trying to update one table value with values from another table, and I cannot get it to work. What am I doing wrong?

This is my SQL-command:
UPDATE tabel1 SET tabel1.name=tabel2.name WHERE tabel1.ID=tabel2.ID

---

Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....

---

QUERY One Table And Update Another In ONE Statement?
ZIP_CODE:
zip_code
latitude
longitude

customers:
customer_id
...
zip_code
latitude
longitude

The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.

---

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

---

Update With Values
I would like to update an existing row from table A with some values from another row in table A.I've tried several things, also something like the combined "insert into/select" however it did not work saying its not valid SQL.
UPDATE A SET value1=b1.value1, value2=b1.value2 SELECT value1, value2 FROM A;
Any idea what could be wrong? Is the idea valid in SQL after all?

---

Update And Add On Values
I have a table that has acculumative hours for each member. I want the members to be able to type in two times, then the code to get the difference between the times and add it on to what is already in the database under their name.
Is this just an UPDATE query, or is there more specy stuff involved?

---

Update Values
I made a mistake converting some time data to unix timecodes while adding some data to a database, and now I noticed some of these dates need to have 15 hours added to the timestamp. So I though about doing a
Code:

UPDATE ntk_news SET date=date+54000 WHERE newsid>0 AND newsid<178

after having a look at the MySQL manual for Update . Did not work, unfortunately.
Anybody knows what I am doing wrong? I took 54000 since that is ~15 hours. The WHERE statement works in a SELECT clause, but then copy-n-pasted into this UPDATE clause it all breaks.

---

How To Update Values In A Column
I have a price list that needs increasing by 10%. Is there any way to do an update commmand and multiply all values in the column by 1.1 to achieve this? I've tried various commands but to no avail.

---

Automatic Update Of Values In Another Db
I am trying to integrate a CMS and a fourm.

As a solution, whenever there is a change in the users table of the CMS db I want to have it automatically changed (updated) in the forum db as well.

Can anyone recommend a solution?

Please note that the two user tables differ in structure, just some columns match, like password and such.

---

Update Values In Column
i have a table with about 500 rows, i need to UPDATE the city column in each row.
I want to remove a comma (",") a space (" ") and the 2 letter state abv ("FL") from all values in the column (named 'city').

example: "Miami, FL" should become "Miami"

---

UPDATE Multiple Values - Proper Syntax?
I use powerbuilder with mysql database.I want to get server's datetime.

---

Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate
update" clause with insert command.

my table "data" has two columns, field1 and field2, where field1 is the
index and is "unique".

when I run

insert ignore into `data` ( `field1`,`field2`)
values
(1,2)
(2,6)
(2,9)
(5,1)

I got
1,2
2,6
5,1

because the third value pair, (2,9) was ignored due to duplication in
field1.

here is what I want to do: when inserting new record with duplicate
index, I want the existing record gets updated. in the above case, I
want the output look like the following after the insert command:
1,2
2,9
5,1
after searching mysql online document, I only found the "on duplicate
update" clause can do something similar, however, all the examples only
show setting the duplicate record to somthing unrelated to the new
values.

---

How To Insert And Update Multiple Values In One Record
How do I insert and update multiple values in one record. e.g.

1. How do I insert "dogs,cats, snakes" in one record
2. If I have "dogs, cats, snakes" in one record and I want to update it and add 'pigs' to make it "dogs, cats, snakes, pigs", how do I do that?

---

Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read.

OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11).

With each shoe it does (or is meant to do) the following:
1. The PHP script that runs the query is looping through every size outside of the query
2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on
3. When it finds the size it is on, it then deducts the correct number of units from the stock table
4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before)

Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not.

Here is the query I have so far (with a little simple PHP around it doing the loop):


PHP

$shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11');
    $numshoesizes = count($shoesizes);
    
    for($i = 1; $i < $numshoesizes; $i++) {
        $stockupdate = "
        UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." =
            (SELECT
                CASE
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12
                THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13
                THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1
                THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2
                THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3
                THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4
                THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5
                THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6
                THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7
                THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8
                THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9
                THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10
                THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11
                THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price)
            FROM items_ordered WHERE items_ordered.StockUpdated = 0)
                
            WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name))
        , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails

        
        // update stock for size $i
        mysql_query($stockupdate);
    }

Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.

---

How To Sum Two Aliased Values In A Query
I need to sum two aliased values in a query in order to order the results.

The query looks like this:

---

Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows..
I want like distinct values from three tables with ic number.
Which is posted by user...

"SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode,
DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1,
DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate
FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'";
This query return many rows i dont know why, i want only one row which is
inserted by the user as customer id.

---

SUM Query And Comparing Values In WHERE Clause...
Here is my query:

---

Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names'
as char(20). I would like to do something akin to:

select namelist.names as mynames, left(mynames,2) as initials;

In this example, I could just do left(namelist.names,2), but in more
complex cases a value retrieved may have had a more complex logic behind
it, e.g., if a bunch of nested if() statements. It would seem logical
that if a value is already retrieved then I should be able to refer to
it within the same query, assuming that the original value does not need
to be recalculated. I know I could use a view to do this, but it adds
another layer of complexity, and I am not sure that the values would not
be recalculated each time the value is needed.

If someone could point out to me the correct terminology for this kind
of thing I believe I should then be able to look up the information
myself. My efforts to find this information on google and the mysql
website were unsuccessful.

---

Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column.  The reason is because I have a column that just contains just the YEAR of a given report.  I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).

---

NULL Values In SELECT Query
I need to know how to push null values to the bottom of a query sorted by ASC.  I know if you sort by DESC the null values are placed last.  How can I push the null values to the end of a query while still sorting using ASC?  Anyone?

---

Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure.

I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs.

For example, if this is my table:

---

How To Generate Sequence Values During Query
I have query like below:

SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no

as result:

part_no part_nm qty

aaa asdfd 3
abab sdfsdf 4
abab adfdf 5

Is it possible in mysql to generate sequence number using query, so the result will be like below :

1 aaa asdfd 3
2 abab sdfsdf 4
3 abab adfdf 5
... etc

---

Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories.

What I want the query to return is this:

Date | ShopperID | VATable Amount | Non VATable Amount|
2006-3-1 | 802135 | 146.95 | 54.00 |

Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above.

I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product:

+------------+----------+-----------------+---------------------+
| Date | ShopperID| VATable Amount | Non VATable Amount |
+------------+----------+------------+---------------+
| 2006-09-04 | 805284 | 0 | 64.00 |
| 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a
| 2006-09-04 | 805287 | 3.25 | 0 | customer has
| 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products
| 2006-09-04 | 805287 | 4.95 | 0 | - I need total
| 2006-09-04 | 805327 | 0 | 53.95 | under each
| 2006-09-04 | 805335 | 0 | 58.95 | ShopperID
| 2006-09-04 | 805414 | 0 | 64.95 |
| 2006-09-04 | 805414 | 3.25 | 0 |
| 2006-09-04 | 805414 | 0 | 64.00 |
| 2006-09-04 | 805414 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.95 |
| 2006-09-04 | 805423 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.00 |
| 2006-09-04 | 805423 | 3.25 | 0 |
+------------+-----------+----------------+--------------------+

Heres the query currently getting these results (without the GROUP BY ShopperID).

SELECT Date,orders.ShopperID,
CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag'
THEN items_ordered.price -- If it is an accessory add VAT
ELSE 0
END AS 'VATable Amount',

CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?('
THEN items_ordered.price -- If size 1 - 6 add price to other column
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?('
THEN items_ordered.price -- If size kids 12/13 add price to other column
ELSE 0
END AS 'Non VATable Amount'
FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <=

&#55614;&#57158;-9-31');

---

How Can I Return (x,y) Query Values To Create A Graph?
Im trying to build a graph of values from a counterTable. (Eg - get a list of how many times a page was requested in a week period with each y value being a day).

If the table looks like this...

---

A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help
me with a problem I'm having. I do not know whether this is possible or
not...

I have a large table of stock price data which is straight-forward
enought. I can select prices based on a ticker and date ranges.
However, what I'd like to do is to select prices only when, say 75% of
them are non-zero (with the goal of eliminating new/suspended/delisted
stocks).

Of course I could just select where price > 0, but then I might get
only a few rows where this is the case. What I would like to do is
always get the full date range of prices, but only if >75% are there.

---

How Can I Return (x,y) Query Values To Create A Graph?
Im trying to build a graph of values from a counterTable. (Eg - get a list of how many times a page was requested in a week period with each y value being a day).

If the table looks like this...

---

INSERTing Into A Table, With Values From Another Table
I'm programming a shopping cart application, where the products are categorized into different lines. I have a table that defines the different product lines, and now want to populate the product table.

However, the text file with the products in it, which I will use to populate the products table, uses the line NAME for each product, when I wish to use the line ID instead. To get the line ID, I would need to get the value from the line table.

My question is: is it possible to populate the products table with data from the products text file, as well as a column from the lines table?

That is, the INSERT (or LOAD DATA) statement will insert column values from the text file, but the line id from the lines table instead of the line name found in the file (WHERE line name = line name in the lines table).

I'm still not sure whether to use an INSERT, and write PHP code to go through the text file, or to use LOAD DATA for this task, because I don't know which statement would support what I'm looking for.

---

Insert Table Values From Another Table
I have two tables: Users and metadata

Users:
id
userid
username

Metadata:
id
userid
value

I would like to extract a list of the userid's from the users table and enter them into the metadata table, with an additional value.

Example:
I want to take all of the userid's from the User table and insert them into the Metadata table, and make the value="one" for each entry into the Metadata table.

---

Inserting Values From 1 Table Into Another Table
I have 2 tables: A and B
Table A contains fields u, x and y
Table B contains fields u and z

Table A has all possible variations of u.
Table B has some of the varaiations of u in it.

My problem is that I'm trying to insert the z-values from Table B into Table A's y field and link them up based on the u-values.

I'm running into problems like truncating at row 0.

I've tried the following queries:

update TableA set y =(select TableB.z from TableB where TableA.u=TableB.u)

update TableA,TableB set TableA.y =(select TableB.z from TableB where TableA.u=TableB.u)

---

Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue:

Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months.

Any suggestion to get result when there is no data:

---

Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query:

SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact
FROM surfacings
INNER JOIN
(SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider)
AS tmp
WHERE surfacings.timestamp = tmp.most_recent;

to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....

---

Add Values To Table
I have a table tblMailingList. It has 3 columns; Name, EmailAdd, AutoID.

I am using
mysql> INSERT INTO pet
-> VALUES ('name1','u@me.com');

but I am unsure as to what to do for the AutoID field. If I use the example given, the error message says that the number of fields is different.
How do I get this info into my table?

---

INSERT Values From One Table To Another
In table A i have a.id and name and in table B, i have b.id, a.id(FK) and owns.

Table A
--------------
a.id | name |
--------------
12 | James |
13 | Paul |
14 | Tom |
15 | Andy |


Table B
--------------

b.id | a.id | owns |
--------------------
1 | 12 | Car |
2 | 12 | Bike |
3 | 13 | PC |

Now what i want is to add two new names to table A which will be Tom and Andy and have the same data in table B for Tom and Andy with just different b.id (PK).

How can i insert this new data in table B using insert?

---

Edit Values In Table Through GUI
I wonder which GUI tool I can use if I want to edit some values in a table? I can't find it in Query Browser. Do I really need to execute SQL to make a simple change to a field?

---

Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other.

Say we have two tables (table_1, table_2) each with a column titled "appointments".

I want a list of results for appointments which are in table_1, but not in table_2

I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables:

SELECT table_1.appointments
FROM table_1, table_2
WHERE table_1.appointments<>table_2.appointments;

I've also tried 'NOT IN' for this but the result was just the same.

---

Get The 3 Highest Values From A Table
My problem is to get the 3 highest values from a table

---

Editing Values In A Table
I just moved from SQL Server to MySQL and in the Enterprise Manager in SQL Server I could query the entire table and then modify the values with the table without writing an UPDATE statement.
I cannot do that in one of my tables because the EDIT at the bottom is greyed out. There are other tables where I can make a modification to any value I want within the table.

---

Ignoring Rows According To Values In Another Table
I have the following query:

---

Inserting Values Into A Mapping Table
I am using a mapping table with two primary keys (ID1, ID2). When I try to insert data into this table I get the following error:
Quote:

Violation of PRIMARY KEY constraint 'PK_myTable'. Cannot insert duplicate key in object 'dbo.myTable'. The statement has been terminated.

I am able however to insert values if I do it directly into my table.
Here is an excerpt of my

comm = New SqlCommand( _
"INSERT INTO myTable (ID1, ID2) " & _
"VALUES (@ID1, @ID2)", conn)
' Add command parameters
comm.Parameters.Add("@ID1", System.Data.SqlDbType.Int)
comm.Parameters("@ID1").Value = ID1List.SelectedItem.Value
comm.Parameters.Add("@ID2", System.Data.SqlDbType.Int)
comm.Parameters("@ID2").Value = ID2List.SelectedItem.Value

---

Generating A Table With Missing Values In SQL
I have the following table:

+------+------+------+-----------+
|ID |GROUP |CODE |ANSWER |
+------+------+------+-----------+
|1 |1 |001 |01-01-2003 |
|2 |1 |002 |Yes |
|3 |1 |003 |NA |
|4 |1 |004 |297797 |
|5 |1 |005 |0 |
|6 |2 |002 |Yes |
|7 |2 |004 |297852 |
|8 |2 |005 |1 |
+------+------+------+-----------+

This means that code 001 and 003 of group 2 are missing. Result of the
query should be:

+-------+-----------+-----------+-----------+-----------+--------+
|GROUP |ANSWER1 |ANSWER2 |ANSWER3 |ANSWER4 |ANSWER5 |
+-------+-----------+-----------+-----------+-----------+--------+
|1 |01-01-2003 |Yes |NA |297797 |0 |
|2 |NULL |Yes |NULL |297852 |1 |
+-------+-----------+-----------+-----------+-----------+--------+

The missing values should be replaced by a NULL value in my resultset.
Is this possible? I don't have any clue how my query should look like,
can anyone help me and show me a solution?

---

Eliminating Double Values From A Table
I need to consolidate data from many adress databases, which i  allready sucessfully put into one table1. As Newbie i am reviing the Manuals and tutorials, but in 2 days i learned a lot about the powerfull command, but not how to solve the following question:

Is there a straight forward method to copy data from one table1 into the other table2, but not those rowse, which have same value in Fieldx.

All my Update and INSERT SELECT experiments somehow created more then less data in the target table.

---

Insert Values Only If They Do Not Already Exist In Table
I want to insert some values from one table (A) into another table (b). However there are some values in table A which exist two times in table A. I only want to insert values one time. So If a certain field value also exist in some other row (same fieldname) then I don't want to insert it twice. It doesn't matter which of the two rows will be inserted.

Can this be done with MySQL? Maybe with some Control Flow Functions?

---

Updating A Table To Increment Values
Question, if I want to update all values in a certain column to increment by one, what statement could I use? In quasi-MySQL, this is my pseudocode:

UPDATE table SET fields=[previous_val+1] where [previous_val]>[my_val]

Or do I HAVE to run a SELECT query, increment the value, and THEN Update it?

I was just thinking there'd be an easier way.

---

Accepting Values As NULL When Not In Another Table
Is it possible to perform a SELECT query on multiple tables and simply accept one of the values as NULL if not in another table rather than the whole query failing?

For instance, if "rec_id" exists in "table_1" for certain but it is uncertain if it exists in "table_2", is it possible to return the query with a variable assignment stating whether "rec_id" was or wasn't found in "table_2"?

I am hoping I can do this without a LEFT OUTER JOIN as the query is fairly complex and involves five different tables. Whenever results are fetched I simply need to know if rec_id is or isn't in table_2 if it is found in table_1.

As there are frequently over 10,000 result rows returned it wouldn't be sound to put this into a seperate loop that queries with each rec_id to see if it is in table_2.

I have tried "SELECT table_1.rec_id, table_2.rec_id FROM table_1, table_2 WHERE table_2.rec_id=table_1.rec_id OR table_2.rec_id IS NULL" but this returns no records.

---

An Enum Field Using Values From Another Table
The first table, called genus, contains several fields, the important one in genus is name, and there are others about the individual plant types.

The second table 'plants' needs one field for the genus name, so that I can link tables on this. If there a way to make a foreign key from the genus table into an enum field on the second? My idea is that then, with phpmyadmin or similar I can just select the genus name from a list when entering new plant data.

---

Renumber Mysql Table Values
There are 100000 rows in a table, I need to renumber them from
1,3,6,8,11,14,21,55,888,1000,9999...99999
to
1,2,3,4,5,6,7,8,9,10,11,12,13,14...
etc
I also need to update an associated table, so its going to need to lock the tables, find the lowest available index in table1, then update table1 index, and then update table2 to use the same index as well, then unlock tables

What would be the procedure to do that?

---

Update Column In Table A With The Count Of Another Table
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.

---