QUERY One Table And Update Another In ONE Statement?

Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table.

I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded.

update boxes b inner join messages m
on b.ctnnumber = m.ctnno
set b.consigneerecv = m.CName,
b.consigneerecvdate = m.DateRcv,
b.phrecventered = "Y",
b.PhilStatus = "delivered",
b.prevreleasestatus = b.releasestatus,
b.releasestatus = "delivered",
b.PhilStatusDate = m.smsrecvdate,
b.phdelprice = "0.00",
b.phdelamt = "0.00",
b.recvrelation = m.Relation,
b.APRecventered = m.smsRecvDate
where b.consigneerecv = ''
or b.consigneerecv = 'NA'
or b.consigneerecv is null;

I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this:

Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1),
table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....

---

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).

---

What Would This Update Statement Be?
I have an array of ids formatted like 1,2,3,4,5

I have a table where there is a field that selects maybe a few of those ids, and is formatted like this: 1,3,5

---

Update Statement
I am using excel to update an online mysql table. I am having an issue with my update statement and I don't understand why. When I run the statement, I am getting a run time error. In the description of the error, the statement is reading as I would expect it to - but it is not working.

usqlstring = "(UPDATE company set company = '" & ucompany & "' WHERE prospect_id='" & updateid & "')"

---

Update With If Statement
I'm trying a statement which I cannot figure out what I'm doing wrong on. I've tried every combination that I can think of and stared myself blue with no success.

update cdr set Cost=if(mod(BillSec,6)=0,int(BillSec/6),int(BillSec/6)+1)

The idea is to update Cost with a value depending on the result of a formula. It's an empty field which gets a computed value from other fields. cdr is the table.

---

Update Statement
here is a update statement I have...

UPDATE support_test
    SET cat_id=42
    WHERE cat_id = 4

It works fine, only thing is I need to update more numbers but I don't want to overwrite the updated numbers.  Example, I want to update 42 to 6 but want to update the original 6's to 12. I have about 30+ numbers that need to be changed, any way to go about this?

---

SQL-Update-Statement
I have to update all UA-fields in the table idrz_kennzahl with primary
key idrz, where the same idrz in the table per_verwaltung with the
field Amt=12 is.

I designed following statement but it doesn't work:

update idrz_kennzahl
set UA=1234
where
idrz_kennzahl.idrz =
(SELECT per_verwaltung.idrz from per_verwaltung where
per_verwaltung.Amt=12

---

Update Statement
I am currently updating a table and increasing a column value by 1, the problem is that he don't increase 1 but it does increase the double of the value specified.
Why does it performs the update like this? And how can i solve this problem?

SQL Statement Syntax used: UPDATE table_name SET column_name=column_name+1 WHERE id=1

Version of MySQL: 4.1.7 for Windows installed in Windows 2003 Server.

---

Update Statement
i have the following problem. in a table i have 3 columns. first a nvarchar field (6 characters) which is filled with point of sales (pos) numbers. the second one is an ip-adress like 10.192.17.30. the third column i want to create based on the second column, means i have to substract e.g. 3 of the ip-number. e.g. the ip-numer is 10.192.17.30 so in the third column should stand 10.192.17.27.

---

Update Statement
I have two tables, and I want to take data from one table, and overwrite it onto the second. Unfortunately, my SQL is pretty weak.

The first table has a mediumtext column and a id (primary key) column. The second has an id (the same as the first), a number, and a mediumtext column.

I want to take the contents of the mediumtext field with the highest number and replace the contents of the field in the first column with it with the same id.

My instinct is to do it this way:

update forum1post p
set p.pagetext = (select query1.message
from (select pe.message, max(pe.editnum)
from forum1post_edits pe
group by p.postid) query1)
where p.pagetext = 'Why did you all flame my topic. Screw your ****ing forums.'
and p.postid = pe.postid

However, that does not work, as pe is not recognized.

---

SQL-Update-Statement
I have a litte problem with my sql-database!
I have to update all UA-fields in the table idrz_kennzahl with primary
key idrz, where the same idrz in the table per_verwaltung with the
field Amt=12 is.

I designed following statement but it doesn't work:

update idrz_kennzahl
set UA=1234
where
idrz_kennzahl.idrz =
(SELECT per_verwaltung.idrz from per_verwaltung where
per_verwaltung.Amt=12).

---

Update Statement
UPDATE `zipLocations` SET `z_zipCode` = '06001' WHERE `z_zipCode` =6001 LIMIT 1 ;

Field Type Attributes Null Default Extra Action
z_zipCode int(5) No 0
z_cityName varchar(50) No
s_stateAbbr char(2) No
z_countyName varchar(50) No
z_areaCodes varchar(50) No

I even went through phpmyadmin found the row clicked on the edit button for that row and updated it. and it still doesn't update.. im' thinking it has to do with the data type being INT...

Why doesn't it update it ?.. It says it was successful.
Affected rows: 0 (Query took 0.0002 sec)

---

Update Statement
UPDATE products_to_categories SET products_to_categories.categories_id = 666
FROM products, products_to_categories
WHERE products_to_categories.products_id = products.products_id
AND products.products_model = 'PAC-P6512'

---

Update Statement
Can someone please let me know what is wrong with the following statement?

UPDATE user_attribute, user
SET user_attribute.value = user.login
WHERE user_attribute.userid = user.id;

I keep getting a syntax error but don't see the mistake made.

---

Update Statement
this works on my machine but not where my site is hosted. They do use an older version of mysql could that be problem

update inv, newprice set inv.cost = newprice.price where inv.itemcode = newprice.itemcode

works fine on my machine her ebut on server i get error..

---

Conditional Update Statement
I have a table with a field of ID numbers. I want to update this field and replace the ID numbers. For example I want every record that currently has the ID number 2,5,6,or 9 to be replaced with a 1. Every record with 1 or 4 to be replaced with 2 ...

I can't seem to form the update statement to do this. I can do them one at a time but this won't work because if I change 2,5,6, and 9 to 1 then when I run then update for 1 and 4 it will change them all. Does this make any sense?

I want some way to be able to use a condition...if id=2 or 4 or 6 set it to 1 and if id= 1 or 4 set it to 2.

My failed attempt:

mysql> update client_software set software_id=1
WHERE (software_id=2 or software_id=5 or software_id=6 or software_id=9)
AND software_id=2 WHERE (software_id=3 or software_id=7 or software_id=8)
AND software_id=3 WHERE (software_id=1 or software_id=4);

---

DELETE,UPDATE Or Other Statement In WHERE
I would like to let my admins write WHERE statements. . In panel where you can see all users of my site, admin should have input where he can put some filters in WHERE statment. E.G. standard query is
SELECT login FROM USERS .
and admin would write age > 18 and male = 'f'
. php script would conact it and it would execute
SELECT login FROM users WHERE age > 18 and male = 'f'
I have question about security of this solution. Is possible to put in WHERE statement sobe dangeurous statemetns like DELETE of UPDATE?

---

Update Statement Problem? Plz Help.
I've a simple doubt of whether can or can't i update two tables by using joins as below. the below update statement matched 2 rows one each for two tables but didn't update.

mysql> update audio join pcr_info on audio.audio_sid=pcr_info.pcr_sid set audio.
audio_pid=4896,audio.audio_prgm_num=3,audio.audio_type="MPEGLayer1",audio.audio_
sr=44.1,audio.audio_mode="1_0",audio.audio_bitrate=320,pcr_info.pcr_pid=49 where
pcr_info.pcr_prgm_num=3 and pcr_info.pcr_sid="2501" and audio.audio_pid=4896 an
d pcr_info.pcr_pid=49;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

---

Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using
1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:

---

Special UPDATE Statement
UPDATE some_table st SET some_page_no=((select count(*) FROM some_table WHERE some_table_id<st.some_table_id and some_id=st.some_id and some_type='OK') div 10)+1 WHERE some_id=345 and some_type='OK'

I want to use the above to make a fast page index (10 on each page). I had something similar in mssql and it worked, but in myssql I get this error:

"You can't specify target table 'some_table' for update in FROM clause"

What have I done wrong or can the above simply not be done?

---

Select + Update In One Statement
I have these statements.

$selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

$query = mysql_query($selectdata) or die(mysql_error());
$update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.

---

Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1

data:
$min_time, $max_time, $time, $attempts

so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.

"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .

---

Select And Update Statement
Is it possible to combine a select and update statement in mysql? If
so, what is the syntax.

---

SQL Statement For A Selective Update
I have a table with a field called 'imagepath', in this table is data represented by the two following path types:
1. J:DIRDIRDIR
and
2. 172.21.2.7DIRDIRDIR
What I need to do is udpdate the data to represent the following:
172.21.10.1DIRetc.

In other words don't update the dir paths just update the mapped dirve and old unc to the new unc.

---

UPDATE Statement Doesn't Work
TABLE - abc

ID NAME
___________
1 hello
12 abc


UPDATE `abc` SET `ID`='5' AND `NAME`='hello' WHERE `ID`='1' AND `NAME`='hello'

wherenver I try to update this table using above sql statement, it doesn't work. I just get "Row match:1 Changed:0" message.

---

If Condition In Update Statement
I have a table with three fields - stockLevel, backOorder and allocated (there are other fields but these are the inportant ones here).

Is it possible to create a SQL update query that will deduct a qty from the stockLevel if there is sufficient stock and if not deduct as much as possible and put the this amount on allocated and the remainder on backOrder?

---

Long Old UPDATE Statement
I have the following SQL which gives me the row Im after

SELECT *
FROM staff, staffemails
WHERE staff.users_username='$user'
and staff.staff_id = staffemails.staff_id

thing is I now want to perform an update on the result of this query. Ive tried

UPDATE staffemails
SET is_registered ='0'
WHERE staff.users_username = '$user'
and staffemails.staff_id = staff.staff_id .

---

UPDATE / JOIN STATEMENT
i have problems after updating mysql from 4.x to 5.0.19. This statement should update about 3000 rows but it only does 15 but there are definitely another 2000 rows with an older date and matching nr!

UPDATE tab1,tab2
SET tab2.newdate = NOW()
WHERE tab1.nr=tab2.nr;

I tried the variation with inner joining tab1 as well. same result.
Does anyone has an idea solving this problem? Perhaps it´s a bug?

---

Update Statement Not Working
Why does this simple update statement not work with a mysql version 3.23.49,
it does work when using 4.0.24..... ?

UPDATE listitem,vjbase,list SET listitem.showsec = 7 WHERE listitem.id_vjbase = vjbase.vjbase_id AND listitem.id_vjbase = 3 AND vjbase.id_viduser = 61000 AND listitem.id_list = list.list_id AND listitem.id_list = 1 AND list.id_viduser = 61000

---

MySQL/PHP - SQL UPDATE Statement
I am trying to update one field in my table in MySQL.  I cannot get this field to update.  Can someone possibly tell me what is wrong with my SQL statement?

$SQL="UPDATE umcritsurv SET IQ_Use_Q9='$IQ_Use_Q9' WHERE fldAuto='54'";

---

Help With Simple SQL Statement To Update Zipcodes
I have a varchar(5) field in a database called 'zip_code'. I imported some zipcodes from an Excel spreadsheet, but the spreadsheet had truncated any zipcode that started with a "0" and removed the zero, so the correct zipcode of "08993" was incorrectly placed into the record as "8993".

Would someone help me write a SQL statement to run on the field zip_code that would look at the data, if it was four characters long, add a "0" to the start and keep the remaining four characters in place?

---

UPDATE Statement To A Single Position
I am running into problems trying to write SQL to update a single position within a field for throusands of records.

Thousands of records on a table have an incorrect number in a field (UIDY) and I am looking for the most simple way to update them all.

If it were only a single record, I would execute the following:
Update MF_VEND_ADDR_DOC set UIDY = '&4027&7608&7607&1609&VC19&VC19000055&&0&999&M' where UIDY = '&4025&7608&7607&1609&VC19&VC19000055&&0&999&M'

The only difference in those two lines is that I want to change the &4025& to &4027&.  

Does anyone know of a way to update thousands of similar records by updating the 5th position in the UIDY field, thus eliminating the need to list out each UIDY value?

---

CASE Statement In Update Command
I'm trying to update one table (tbl_usage) based on information from another table (tbl_subscriptions) joined by the userID. The usage table tracks the number of times a user hits a page, and decrements the count each time. When the user has a zero count, they are redirected to a warning page.

Each week the count is reset depending on what type of license they have (licenseType) and how many of those licenses they've bought (numLicenses). A group license (G) gives them 100 hits, and an individual license (I) gives them 5 hits.

I'm trying to use a case statement to perform this count reset operation, but I can't seem to get it to work. The following case statement works just fine: Code:

---

Convert A Nested Update Statement
i have a nested update statement in my sql server as follows
UPDATE DIM_EVENT .

SET INVOICE_AMOUNT =
(SELECT SUM(INV.INVOICE_AMOUNT_TOTAL)
FROM INVOICE INV
WHERE INV.INVOICE_REFERENCE_NUMBER = DIM_EVENT.OBJECT_ID
AND INV.INVOICE_AMOUNT_TOTAL IS NOT NULL)
WHERE DIM_EVENT.EVENT_TYPE_CODE IN ('OFULN', 'OAUTH', 'CRDRL');
what is the equivalent in MySQL.

---

Nested Select In Update Statement
my nested Select-Update-Statement doesn't work. I think it's because the SELECT-Statement returns an empty set.. is there a posibillity to run this statement if there is no empty set and insert NULL if it is? Or is there another error at all?

CODEUPDATE proj_user_suche SET acronym = (SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"

---

Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table.

SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'

---

MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.

---

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.

---

Query Statement
I am actually using this for a different database, but don't know what I am getting wrong with my syntax.

select * From rents where id = 119,505 order by Town

I keep getting an error message.
Can anyone see where I am wrong?

---

Statement In A Query
Is it possible to do an if statement in a query? or something like it?
I have a schedule in a table. For the time, I need it to output local time and eastern time. I have the local date and time in a "datetime" column. So depending on where the event is, I need too add to the time in order to produce an eastern time as well.
Right now I'm using this:

DATE_FORMAT(DATE_ADD(sked.date, INTERVAL +2 HOUR),'%l:%i%p') AS eastt

But I need to change the interval number depending on where the event is being held.
Does anyone know how? or is there an easier way to accomplish this task?

---

Technically An If Statement In A Query?
My heirachy is Forum connected to thread via forum_id and then Post connected via thread_id.

Some forum rows have require_access set to 1, if that is the case then user must have a row in user_data with a key of ForumAccess, the value as the forum id and the user_id as there user id.

I am retrieving a list of threads from the entire forum, however I need to make sure users can't see threads which are in forums they cant access. Here is my query so far.


MySQL
SELECT `thread_id` as `id` FROM `Post` p
            JOIN  `Thread` t ON (t.`id` = p.`thread_id`)
            JOIN `Forum` f ON (f.`id` = t.`forum_id`) 
            LEFT JOIN `User_Data` u ON (u.`user_id` = p.`user_id`)
            WHERE u.`key` = 'ForumAccess' AND u.`value` = f.`id` AND f.`require_access` = 1
            GROUP BY p.`thread_id`
            ORDER BY max(p.`updated`) DESC LIMIT $from, $to

I'm unsure on what route I should be going, as essentially I need some sort of if(f.require_access = 1){check user_data row exists if it does then return this thread}

---

Using IF Statement In MySQL Query
I have the following code in one of my scripts.

PHP

$result = @mysql_query("SELECT * FROM Table WHERE id='$id'");
$row = mysql_fetch_array($result);
if ($row['name'] != "") {/* do nothing here */}
elseif ($row['email'] != "") {$row['name'] = $row['email'];}
else {$row['name'] = "&lt;anonymous&gt;";}

Like you can see I'm using an IF statement outside the MySQL query to define the variable $row['name']. But I know that I can use an IF statement inside the query instead of outside it.

---

Downgrade To Non Sub Query, But How To Change This Mysql Statement?
I've to downgrade my mysql script from sub-query(ver 4x) --to-> non sub-query(ver 3.32)

I manage to changed below sub-query --> non subquery...

---

Mysql Query Browser...how Do I Ignore " Within A SET Statement?
I'm trying run this in mySQL query browser

Update xcart_classes x
Set classtext = "5 Year Protection <a href="javascript: void(0);" onclick="javascript: window.open('etp.htm','HELP_POPUP','width=406,height=261,toolbar=no,status=no,scrollbars=yes,resizable=no,menubar=no,location=no,direction=no');" class="PopupHelpLink">Learn More</a>"
where Classtext = "Add 5-Year Extended Protection";
Obviously it fails, and I'm pretty sure it's because of the double quotes on my SET statement...any idea on how to avoid 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.

---

Update Query
I have a query question. I want to update a database that contains ips like 90.30.100.xxx. I only want to modify the third numer (100). Does anybody know how can i do this?

ex: i want to modify all ips from 90.30.100.xxx to 90.30.101.xxx. Notice if i had 90.30.100.100 i'd like to become 90.30.101.100

---

Update Query
I'm selling tickets and I need to be able to reduce the total number of tickets when one is sold. I have a table with the total number of tickets stored in a field and and a field with the quantity sold in a particular purchase. I think this is a fairly simple update query can anybody help get me started?

---

Update IN Query
Code:

UPDATE SET COUNT=COUNT+1 IN WHERE SELECT
sponsor,mstatus, count( username ) from customer Group by
sponsor DESC Having count( username ) = 2

---

Update Query
I am trying to do a custom sortup/sortdown function in php on a mySQL basis. Now I got that prob:

I got a table, that has a "sort"-field for each data like that:

data | sort
a | 1
b | 2
c | 3
d | 4
e | 5

The output of the data is ORDER BY sort ASC. Now I want the users allow to change the "ranking"/"sorting" themselves that way, that they click e.g. on sort entry d one up.

So the new table should look like that:

a | 1
b | 2
c | 4
d | 3
e | 5

Does anyone know how to do that in one or only a few mySQL queries? I mean I could read out the whole table and then do a php-loop giving new sort-numbers to each data and then update every single data, but I guess there should be an easier way in mySQL. My prob is that I suck big time at mySQL

---