Update Using Concat

Help With UPDATE Using CONCAT On Longtext Fields
I have two tables with longtext fields that I'd like to concatenate and update in one of the tables. Not sure if you can use concat on a longtext field. Here's what I tried:

UPDATE `node_revisions`, `weblink`
SET `node_revisions`.`body`=concat(`node_revisions`.`body`,"<br /><a href="",`weblink`.`weblink`,"">Visit this site</a>")
WHERE `node_revisions`.`nid` = `weblink`.`nid`;

This is the scenario. I have these 2 tables:

node_revisions table
-----------------------------
| id | nid | body (longtext) |
-----------------------------
| 1 | 23 | this is some text |
-----------------------------

weblink
-----------------------------
| id | nid | weblink (longtext) |
-----------------------------
| 5 | 23 | http://somesite.com |
-----------------------------

I'm trying to use UPDATE to concatenate the node_revision.body and weblink.weblink fields, where they share foreign key nid. So the resulting node_revisions would look like:

node_revisions table
-----------------------------
| id | nid | body (longtext) |
-----------------------------
| 1 | 23 | this is some text<br /><a href="http://somesite.com">Visit this site</a> |
-----------------------------

When I run the UPDATE statement above, I get the following feedback, but nothing has in fact changed:

Query OK, 813 rows affected (0.15 sec)
Rows matched: 813 Changed: 813 Warnings: 0

---

Concat Maybe?
Theres a way in mysql to get a comma separated list of fields in a one to many relationship - rather than multiple rows for each record in the 'one' table. But I can't remember what it is, I thought it was concat but that didn't work... I've done it before but I can't remember what it was or why.

---

Using CONCAT
Is it possible to make query which would select some value even column would be NULL/EMPTY ?

Example:

Table
name

John
NULL
Mike

SELECT CONCAT_WS('something',name) FROM table;

results:
Johh
Something
Mike

---

WHERE With CONCAT
I have a table A with a (varchar 20) A.field and a table B with two (varchar 10) fields: B.field1 and B.field2.

How can I make a SELECT with a WHERE of this type:

WHERE A.field = CONCAT (B.field1 , B.field2)

I get an error in CONCAT. How is the string concatenation in mySQL?

---

Concat
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

Any Ideas?

---

Using Concat()
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

---

Use CONCAT
I'm having lots of problems using the CONCAT instruction. I'm trying to store in a variable (@f) a COUNT, but the system always crashes and I don't know why. Here you have the lines:


set f=0;
set @f:=f;
set @stmt3:=CONCAT("select count(*) into `",@f,"` from `",@n,"` where sourceurl=`",@b,"` and link=`",@c,"`");
prepare query from @stmt3;
execute query;

---

Concat
I'm having problem using concat

select CONCAT(firstname,' ', lastname) as fullname from tbltable where fullname = 'name';

It say Unknown column 'fullname' in 'where clause'. It is my sql wrong or what?

---

How To Use CONCAT In The 'FROM' Section
For example I'd like to use the following query:
SELECT * FROM CONCAT('table_', 'name');

Is there a way to do this? or something similar?

---

How Does Mysql_field_len Act On CONCAT?
I am quite puzzled by this. I have two fields which if selected by themselves will return a field length of 2 using mysql_field_len. However if I select them as CONCAT(field1,' ',field2), the field length is much greater than the 5 I would expect. It looks to be about 20. Is there a way to control this length? Where is it getting the field length from?

EDIT: Nevermind, this can be done using CAST(CONCAT(field1,' ',field2) AS CHAR(5))

---

Concat Two Tables
I have two tables, one containing payments to a supplier and one containing
invoices issued by a supplier. I'd like to get a total from each table with
one query.

I need somehow to list all payments and all transactions together in one
table result set.

i.e.....

---

Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear)
FROM tblmonths, tblyears
where CONCAT(tblmonths.fldID," ", tblyears.fldYear)
NOT EXISTS
(SELECT CONCAT(fldMonth, " ", fldYear)
from tblexpensesclaims)
GROUP BY tblyears.fldYear, tblmonths.fldID

---

CONCAT And Variables
I have the following statement:

------------------
SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c;

set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')");
prepare query from @stmt3;
execute query;
--------------

I think that I'm using correctly the CONCAT function but it doesn't work.

---

CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35


The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

---

Using CONCAT For 2 Tables
Currently I'm using this SQL query to connect 2 tables but now, I need to add a 3rd table in the same manner as the "links" table in the following query.

SELECT * FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') ORDER BY tracker.occurred DESC LIMIT 0, 15

---

Problem With Concat
I have the following select:

SELECT distinct name, main.ACCOUNT,
concat(FlatNo,' , ',FlatName,' , ',number,' , ',`St Name`,' , ',SuburbName,' , ',`Town Name`,' , ',Stand) AS Address
FROM ...

I need to return all the available address information that we have but the select returns only about 15 addresses in a DB of about 100000. Some of the address fields do contain null values and the SELECT CONCAT is returning null for the whole row if any field contains a null value.

Is there anyway round this? I have tried IFNULL(Field,"") inside the concat but this does not seem to work either.

---

CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35


The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Please let me know if/when this will be fixed, and if there is a
work around that might be used, r a better way to insert BLOB data is
known.

---

CONCAT All Columns
I want to produce a query that does something like this:

SELECT MD5(CONCAT(*)) FROM table WHERE 1

* is all the columns.
I don't want to have to type in every column name, but have MySQL do it.

---

Problems With Concat
I'm having problems with Concat. I have this piece of code:

set @m:=a;
SET @stmt1:=CONCAT("INSERT INTO ",@m," VALUES (b,c)");
PREPARE query from @stmt1;
execute query;

b and c are cursors and when it tries to execute the query and I get the following error:

"Unknown column 'b' in field list"


It seems that is not doing correctly the CONCAT?

---

Concat For Numbers
I am interested in performing a concat for numbers with a space
e.g.
select concat (num1, ' ', num2) from tablex as numconc

such that if num1 is 123 and num2 is 456 the result would be '123 456'
I believe concat should work for a string rather than number format, however having created the fields num1 and num2 in string format it still does not work.
Any advice on why this isn't working, or an alternative method to achieve the result would be most welcome.

---

Help With Subquery / Group Concat
I've attached some data from which I want to extract individual itinerary ids and show the ships which go to those destinations. The problem is that the destination ids are stored as comma separated values in a single field and there are rows which duplicate the same associations between the ship and destination ids. I can do:

SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY ship_id;

and get a list of destination ids for each ship but what I actually want is a list of ships for each destination Id.

Can somebody give me some pointers on how to achieve this? I think it might be possible with a subquery but am not sure how to go about this. MYSQL version is 4.1.20.

---

SELECT CONCAT() Subquery
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query?

---

SELECT Subquery CONCAT
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query with MySQL?

---

CONCAT () Returns BLOB
MySQL ver. 3.23.49

SELECT tblHrsClass.classid , CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl FROM tblHrsStudent LEFT JOIN tblHrsClass ON tblHrsStudent . classid =tblHrsClass.classid GROUP BY tblHrsClass . classid HAVING COUNT(tblHrsStudent . classid )<19

The query above is broke. I'm using it to build a list box.

I can run the query without the CONCAT function and pulls the expected data.

When I add CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl,

class_ttl is returned as a BLOB of a certain size.

---

Need To Concat Column And DATE_FORMAT
I'd like to combine the title and starttime so I can distinguish amongst several requests with the same title. ie:

ID=7116
name=Testing11-26-2007

Here's my query:
select ID, title+DATE_FORMAT(starttime,"%m-%d-%Y") as name from requests where ID=7116

Which gives me:
ID=7116
name=11

What do I need to do to get the result of DATE_FORMAT into a form that will concat with the text from another column?

---

Concat 2nd Table Results Into First?
Given three tables used to track multiple email addresses and phone numbers for a person:

Table 1: id, name, address
Table 2: table1_id, email
Table 3: table1_id, phone

How can I do a select to end up with all emails and phones concated together into a single row per individual:

id, name, address, "email,email,email", "phone,phone"

The solution needs to work with MySQL 3.23 (or possible MySQL 4.0). 4.1 is NOT an option, so group_concat is out.

---

CONCAT Length Limitation
I have encountered a problem while using the CONCAT command to update a column with a size over 1mb, the existing information is lost. I can't seem to find a suitable alternative to update a field in a row with several appends. the data length in total will be exceeding over 16mb at times.

---

Concat A Field In Up To 4 Rows?
A customer can have between 1 and 4 rows, is it possible to make a query that will merge field "A" of all rows and then discard all other rows leaving only 1 row left with the merged data in field "A".

---

GROUP CONCAT Issue
Iam using Mysql Version 5.0.

the query,

SELECT GROUP_CONCAT(field_name)FROM TABLE1;

This returns BLOG as result set in the editor in my Toad.

What is the issue, i have just 300 records in my table.

Why this happens, please help

---

Nesting A 'concat' Within A 'replace'
I have the_table:
col_1 , col_2
-------------------------
aaa , aaa_do aaa_dat

the query that I am using:"update the_table set col_2=replace(col_2,col_1,concat('hoorah_' , col_1) ) "

I want
col_1 , col_2
-------------------------
aaa , hoorah_aaa_do hoorah_aaa_dat

however the query does not successfully stick the 'hoorah_' in front of the aaa_dat; instead I get this:
col_1 , col_2
-------------------------
aaa , hoorah_aaa_do aaa_dat

---

Problem W/concat To Date
I am trying to run a report that displays data that falls between a date range. The dates are entered in mm/dd/yyyy format. I convert the date using ColdFusion code for MySQL to use:

cfset Start1c = DateFormat(#Start1#, "yyyy/mm/dd")
cfset End1c = DateFormat(#End1#, "yyyy/mm/dd")

The query that uses this variable will run as long as I,
1. use slashes in the date variable above
2. query for data that falls in the same year

Here is the part of my query that has a problem w/the formatting of this date:
date_format(date_sub(concat_ws('-',dataYear,dataMonth,'02'), interval 1 day), '%Y-%m-%d')
BETWEEN '#Start1c#' AND '#End1c#'
AND InputMethod = 1

dataYear and dataMonth are integer fields combined to create a date.

---

INSERT With Concat Auto_increment
I have a table that stores online orders. Each row in the table uses auto_increment to create a unique id for each order, in a field "id".

Each order (row in the table) also has a serial_number field that has a string representing the product and a unique number representing the order. I would like to append the id field value for the newly created order to the product string, to create the serial number.

Can this be done within a single SQL statement or do I need to insert the order, find out the id, then update the order and appending the id on the serial number string.

Is it possible to append the id of the newly inserted row to another field in the row in a insert statement?

I tried INSERT INTO orders SET serial_number = CONCAT('product text',id); but MySQL did not like the id column in the concat function.
Do I need to use LAST_INSERT_ID to find the id?

---

SELECT Where String Matches CONCAT Value?
I'm setting up a database where an order number is concat() of the company ID + order ID + timestamp year (2 digit). How would I find a string match to the order number without storing it explicitly in the database? Here's a simplified version of the 2 tables involved...

CREATE TABLE `companies` (
`id` int(10) unsigned NOT NULL auto_increment,
`prefix` varchar(3) NOT NULL,
`description` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `orders` (
`id` int(6) unsigned zerofill NOT NULL auto_increment,
`company_id` int(10) unsigned NOT NULL,
`time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

...and here's what I was trying to find an order number match:

SELECT orders.id
FROM orders
LEFT JOIN companies ON companies.id=orders.company_id
GROUP BY orders.id
HAVING (
CONCAT(companies.prefix, orders.id, '-', DATE_FORMAT(orders.time_stamp, '%y')) LIKE '%whatever%'
)

But I'm getting an error "#1054 - Unknown column 'companies.prefix' in 'having clause'". If I remember right, it's because I'm not selecting the fields from the having clause, and they're not in the group by statement. So, how would a fellow do what I'm trying to do? Should I just accept the redundancy and insert the full order number into the database?

---

Concat Columns Based On Certain Criteria Using Only SQL?
I have a big table (15 million records). Each row has a parsed address (number, prefix, street, type, suffix) and I need to take these separate pieces and concatenate them. However, often times a few of those pieces will be blank, so I want to prevent any unnecessary spaces.

I'm using MySQL 4.1. I found some documentation for using if statements for MySQL 5.0, tho I dont know if any of it works in 4.1.

The basic psuedo code of what I want to do is this:

---

Create &amp; Store A Concat String
I have two tables:

keywords(itemID, WordID) and
words(WordID,Word)

I would like to create a single string of the words associated with a single ItemID by concatenating words and store them into a new table wordstring(itemID, wordstring).

---

Illegal Mix Of Collations When Using The Concat Function
SELECT concat('hello world ', cui)
FROM `umls_cuis`

I am using MySQL 4.1.3-beta. I get the following error when trying to execute the above SQL:

ERROR 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'concat'

What can I do about it?

---

Usage Of CONCAT Operator When Creating A View ...
I am trying to create a view and my syntax is as follows :

CREATE OR REPLACE VIEW UDT_GRP_LDR_ACTIVE_V
( PRIKEY
, Leader
, Group_Name
, Group_Description
, Meeting_Frequency
, Meeting_Time
, Meeting_Day
, Sector
, Focus
, AUTO_IMAGE
, AUTO_SECURITY_AUTH )
AS
SELECT A.PRIKEY
, CONCAT (P.PER_FNAME, ' ', P.PER_LNAME) as Leader
, G.GRP_NAME
, G.GRP_DESC
, G.MTG_FREQUENCY
, G.MTG_TIME
, G.MTG_DAY
, G.SECTOR
, G.FOCUS
, P.AUTO_IMAGE
, A.AUTO_SECURITY_AUTH
FROM UDT_PER_GRP_ROLE A
LEFT OUTER JOIN UDT_PERSON P ON A.PER_ID = P.PRIKEY
LEFT OUTER JOIN UDT_CELL_GROUP_ACTIVE_V G ON A.GRP_ID = G.PRIKEY
WHERE A.STS_ID = 1
AND A.ROLE_ID = 4;

When I run this statement, I get the following error (in PHPMyAdmin) : "#1305 - FUNCTION hart7511_sope1.CONCAT does not exist".

At first I thought it was a permissions issue on my account and contacted the SysAdmin at my web hosting company. I'm still waiting for a response from them, but the strange thing is that when I just run the SELECT statement on its own (without the CREATE OR REPLACE VIEW part), it runs fine ... so despite the wording of the error message, the problem doesn't really seem to be with "CONCAT" ... or am I missing something ? BTW I have created other views without using CONCAT and had no issues.

---

Can You Select And Concat Multiple Items Separated By "," ?
The select I have finds multiple values for a field. I want to take that field and create ONE field separated by commas.

Is that possible? I could do it in PHP but it will be easier programmatically if I can do it here.

For example:

HTML
Select field1 where x=2;
val1
val2
val3
val4
I want it to be like:

HTML
Select field1 where x=2;
val1, val2, val3, val4
where "val1, val2, val3, val4" is ONE field I can then use.

I am actually using this as a subquery so it's not as easy as php's explode/implode.

---

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.

---

Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master.

What is the best method of re-synching the databases?

Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?

---

Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server:

Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database.

I can connnect and read the data and data definitions just fine.  I cannot insert, update, delete, create, or alter any table in this database.

Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy.  I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.

---

ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute.

Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table.

The format of the table is like this (with some example data to show you more clearly):

+-----------+-------------+---------+------+--------+------+
| ShopperID | Product Code | Product | Price | Amount | Units |
+-----------+-------------+---------+------+--------+------+
546733 1315 prod 1 64.00 64.00 0
546733 1316 prod 2 43.99 43.99 0

OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right.

Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do:

1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper
2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists.
3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing.

Does anyone have any ideas on how I can get round this?

Here is my PHP function which contains the query:
PHP

// this function for insert data to csv
function makeINSERTS($text, $table, $tablefields, $correctcsv){
    $insert = array(); //make array for hold data insert
    $i = 0;
    $success = true;
    
    while(list($key, $val) = each($text)){
        // Insert the data
        $insert[$i] = "INSERT INTO ".$table."  VALUES('";
        $insert[$i] .= implode("','", $val);
        $insert[$i] .= "') ON DUPLICATE KEY UPDATE ";
        foreach($tablefields as $k=>$field){
            $insert[$i] .= "`$field`='{$val[$k]}',";
        }
        $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1);
        // echo $insert[$i].'<p></p>'
        $result = mysql_query($insert[$i]);
        if(!$result) {
            echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />'
            $success = false;
            die('Query failed: ' . mysql_error().'<br />');
        }
        $i++;
    }
    if($success == true && $correctcsv == true){
        echo 'SUCCESS in uploading the CSV file to the database!<br />'
    }
    else {
    }
return $insert;
}

---

Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared.

Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly?

We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.

---

Need To Update PK Of Every Row
I want to update every row's primary key and need an SQL statement to do it.

There are 2 columns
PK
Placename

So I firstly want to order all the rows by placename in alphabetical order and then starting by updating the first row to 1, I then want to update every ID with an increment of one - all the way until the final row is updated.

---

Log Update In My.ini
If i put

log-update=update

in my ini it works fine,but as soon as try to specify a different location i.e.

log-update=/backups/update

i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work.

---

Update If?
I have a table that has an item field, a price field, a price min field, a price max field.

I want to be able to store the current price for an item as well as it's high and low. Is there a way to do an update on the row for the new price as well as compare the new price to the min field and max field and then update them if the new price is either higher or lower than the current values in the min and max field?

I know how to do it with multiple queries and comparing, but am looking for a clean way in one query.

---

Last Update
Is there a way in mysql to discern when the last date/time a field, row, or
table was been updated or inserted?

---

Can't Update
I'm current using MySQL and ASP.

But there seems to be a problem when i try to update a particular record. I get the following error message.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Query-based update failed because the row to update could not be found.

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM MainPageContents " & _
"WHERE BelongsToShop=" & Session("ShopID") & _
" AND ID=" & which

rs.Open sql, conn, adOpenKeyset, adLockOptimistic

rs.update
rs("some_fld") = "something"
rs.update

I tried setting cursor locations to aduseclient and aduseserver but to no avail. There's also nothing wrong with the query. Does anyone know if there's any kind of issue on updating MySQL using ASP?

---

Getting A SUM In An UPDATE
I'm trying to update a "parent" table with the sum of data in a "child
table". To illustrate, let's say our database was modeled around a book:

book
-> chapter
-> page
-> attachment (pictures)

The -> represents a one-to-many relationship (e.g. a book has chapters).
Each attachment has a filesize and the parent book has a total_size
(denormalized) column that is the sum of all it's children attachments. I'd
like a query to update total_size in the book table.

Below is what I've come up with thus far. Obviously, it's not working, and
hence my post. The nested SELECT query works fine, but I'm receiving a "You
can't specify target table 'book' for update in FROM clause" error. Code:

---