UPDATE ... FROM query
I have these tables: CREATE TABLE stack ( ProjectID INTEGER NOT NULL StatusID INTEGER NOT NULL ItemNumber INTEGER NOT NULL Stamp DATETIME NOT NULL ); CREATE TABLE queue ( ProjectID INTEGER NOT NULL StatusID INTEGER NOT NULL ItemNumber INTEGER NOT NULL Stamp DATETIME Wait BIT NOT NULL ); And these rows (date is DD-MM- hh:mm:ss): INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:00:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:15:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:30:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 2, 10, '28-03-2003 17:00:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 1, 10, '28-03-2003 19:15:00'); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 3, 10, '28-03-2003 19:15:00', 0); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 2, 10, '28-03-2003 17:00:00', 0); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 1, 10, NULL, 0); Now I wan't to UPDATE "queue" so it's stamp is set to the nearest higher stamp value in "stack" where ProjectID, StatusID and ItemNumber is equal to "stack" values. If stamp in "queue" is NULL then this should be set to the MIN(stamp) value in "stack, based on the same criterias as before. Then wait should also be 1, for above criterias, but that's not that important now... The result rows in "queue" should look like this after doing the UPDATE: ProjectID StatusIDItemNumber Stamp Wait 1 3 10 28-03-2003 20:30:00 1 1 2 10 28-03-2003 17:00:00 0 1 1 10 28-03-2003 19:15:00 1 I've tried with this query (this is only for StatusID=3, but imagine this run for StatusID 1,2,3): UPDATE queue SET Wait = 1, stamp = S.stamp FROM stack S WHERE queue.ProjectID = S.ProjectID AND queue.ItemNumber = S.ItemNumber AND queue.StatusID = S.StatusID AND queue.Stamp < S.Stamp AND queue.ProjectID = 1 AND queue.ItemNumber = 10 AND queue.Wait = 0 AND queue.StatusID = 3 Plz help me - I don't know how to solve this one... /Jakob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using LIKE to search for occurence of a column value in a string
Thanks!
I know I'm searching for for a long string within a short string - but that's why I
reversed the expression from "column LIKE(value)" to "value LIKE(column)"
But I think I just might go for your solution using the LOCATE function as it seems to
better fit this specific need.
/Jakob
> -Original Message-
> From: Jeff Shapiro [mailto:[EMAIL PROTECTED]
> Sent: Sunday, March 23, 2003 7:46 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Using LIKE to search for occurence of a column value in a
> string
>
>
> The reason that what you are doing isn't working is because you are
> trying to find a really long string in a short string. You need to
> reverse your string searching.
>
> Try:
>
> SELECT URL, Name
> FROM websites
> WHERE LOCATE(URL,
> 'http://www.microsoft.com/kb/knowledgeb.asp?id=3&strse=12') > 0;
>
> Here's some other functions that might be of interest:
> LOCATE(substr,str)
> POSITION(substr IN str)
> Returns the position of the first occurrence of substring substr in
> string str . Returns 0 if substr is not in str :
> mysql> SELECT LOCATE('bar', 'foobarbar');
> -> 4
> mysql> SELECT LOCATE('xbar', 'foobar');
> -> 0
> This function is multi-byte safe. In MySQL 3.23 this function is
> case sensitive, while in 4.0 it's only case-sensitive if either
> argument is a binary string.
> LOCATE(substr,str,pos)
> Returns the position of the first occurrence of substring substr in
> string str , starting at position pos . Returns 0if substr is not in
> str :
> mysql> SELECT LOCATE('bar', 'foobarbar',5);
> -> 7
> This function is multi-byte safe. In MySQL 3.23 this function is
> case sensitive, while in 4.0 it's only case-sensitive if either
> argument is a binary string.
> INSTR(str,substr)
> Returns the position of the first occurrence of substring substr in
> string str . This is the same as the two-argument form of LOCATE() ,
> except that the arguments are swapped:
> mysql> SELECT INSTR('foobarbar', 'bar');
> -> 4
> mysql> SELECT INSTR('xbar', 'foobar');
> -> 0
> This function is multi-byte safe. In MySQL 3.23 this function is
> case sensitive, while in 4.0 it's only case-sensitive if either
> argument is a binary string.
>
>
> At 20:07 +0200 3/23/03, Ville Mattila wrote:
> >> I tried to use LIKE:
> >> SELECT URL, Name
> >> FROM websites
> >> WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3&strse=12'
> >> LIKE (URL + '%');
> >>
> >> But this doesn't return any results. I would like the
> following as output:
> >> 'http://www.microsoft.com/kb/'Microsoft Knowledgebase
> >
> >Hi!
> >
> >How about the following?
> >
> >SELECT URL, Name FROM websites WHERE
> >'http://www.microsoft.com/kb/knowledgeb.asp?id=3&strse=12'
> LIKE CONCAT(URL,
> >'%');
> >
> >- Ville
> >
> >
>
>
> --
> _ ____
> +--+
> / | / /__ _/ /_ _|Jeff Shapiro
>|
>/ |/ / _ \/ __ \/ ___/ __ \/ __ `/|Photography and
> Graphic Design|
> / /| / __/ / / (__ ) / / / /_/ / |Colorado Springs, CO,
> USA |
> /_/ |_/\___/_/ /_//_/ /_/\__,_/ |www.nensha.com |||
> [EMAIL PROTECTED]|
>
> +--+
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using LIKE to search for occurence of a column value in a string
I wan't to search for all rows that match a value. I have these rows: URL Name 'http://www.microsoft.com/kb/' Microsoft Knowledgebase 'http://www.microsoft.com/search/' Microsoft Search Now I wan't to find all occurences where any of above URL columns exist in the string 'http://www.microsoft.com/kb/knowledgeb.asp?id=3&strse=12'. I tried to use LIKE: SELECT URL, Name FROM websites WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3&strse=12' LIKE (URL + '%'); But this doesn't return any results. I would like the following as output: 'http://www.microsoft.com/kb/' Microsoft Knowledgebase Plz help me:) /Jakob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN on same table
Yup! Maybe I haven't explained me correct, but yes, that's what I wan't. This is because as soon as a project is NOT inheriting, then all of it's childs are then also not able to inherit from the original root, because the project that's NOT inheriting breaks the node... But the childs are still able to inherit from the parent that breaks the node - but then they only inherit information from this parent and not from the root. Best Regards Jakob Vedel Adeltoft, CTO [EMAIL PROTECTED] WebProof - www.webproof.dk Tel +45 46 32 68 68 Fax +45 46 35 94 94 > -Original Message- > From: Uttam [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 15, 2003 12:59 PM > To: Jakob Vedel Adeltoft > Subject: RE: JOIN on same table > > > you want only following records, i.e. excluding all childs of > SubProjectB because SubProjectB is not inheriting? > > project_group_id ref_project_group_idname > root_project_group_id node_depth inherit > 100 ProjectA > 0 0 0 > 5710 SubProjectA > 10 1 1 > > > -Original Message- > From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 15, 2003 15:28 > To: Uttam; [EMAIL PROTECTED] > Subject: RE: JOIN on same table > > > Uttam: Your're great! But there's still one thing I haven't > mentioned (my fault)... > > If I add more childs to the same node_depth and sets > SubProjectB NOT to inherit, then the query fails... > > I now have these records: > project_group_id ref_project_group_idname > root_project_group_id node_depth inherit > 100 ProjectA > 0 0 0 > 5710 SubProjectA > 101 1 > 5957 SubProjectB > 102 0 > 6059 SubProjectC > 103 1 > 6359 SubProject_1 > 103 1 > 6260 SubProjectD > 104 1 > 6463 SubProject_2 > 104 1 > > This would look somehting like this in a treeview: > ProjectA (10) <- > node_depth = 0 (root) > \ > SubProjectA (57) > <- node_depth = 1 > \ > SubProjectB (59) > <- node_depth = 2 > / \ > SubProject_1 (63) SubProjectC (60) <- node_depth = 3 > /\ > SubProject_1 (64) SubProjectD (62) > <- node_depth = 4 > > But if running your query against the database I get the > following output: > project_group_id ref_project_group_idname > root_project_group_id node_depth inherit > 100 ProjectA > 0 0 0 > 5710 SubProjectA > 10 1 1 > 6260 SubProjectD > 104 1 > 6463 SubProject_2 > 104 1 > > What could be wrong? > > Best Regards > > Jakob Vedel Adeltoft, CTO > [EMAIL PROTECTED] > > WebProof - www.webproof.dk > Tel +45 46 32 68 68 > Fax +45 46 35 94 94 > > > -Original Message- > > From: Uttam [mailto:[EMAIL PROTECTED] > > Sent: Saturday, March 15, 2003 7:57 AM > > To: Jakob Vedel Adeltoft; [EMAIL PROTECTED] > > Subject: RE: JOIN on same table > > > > > > try this: > > > > SELECT PG1.* > > FROM project_group_list AS PG1 LEFT JOIN project_group_list AS PG2 > > ON PG1.ref_project_group_id = PG2.project_group_id > > WHERE > > (PG1.root_project_group_id=10 AND PG1.inherit=1 AND > > PG2.inherit=1) > > OR (PG1.project_group_id=10) > > OR (PG1.ref_project_group_id=10) > > ORDER BY PG1.node_depth > > > > regards, > > -Original Message- > > From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED] > > Sent: Friday, March 14, 2003 16:30 > > To: [EMAIL PROTECTED] > > Subject: JO
RE: JOIN on same table
Uttam: Your're great! But there's still one thing I haven't mentioned (my fault)... If I add more childs to the same node_depth and sets SubProjectB NOT to inherit, then the query fails... I now have these records: project_group_idref_project_group_idnameroot_project_group_id node_depth inherit 10 0 ProjectA0 0 0 57 10 SubProjectA 10 1 1 59 57 SubProjectB 10 2 0 60 59 SubProjectC 10 3 1 63 59 SubProject_110 3 1 62 60 SubProjectD 10 4 1 64 63 SubProject_210 4 1 This would look somehting like this in a treeview: ProjectA (10) <- node_depth = 0 (root) \ SubProjectA (57)<- node_depth = 1 \ SubProjectB (59)<- node_depth = 2 / \ SubProject_1 (63) SubProjectC (60) <- node_depth = 3 /\ SubProject_1 (64) SubProjectD (62)<- node_depth = 4 But if running your query against the database I get the following output: project_group_idref_project_group_idnameroot_project_group_id node_depth inherit 10 0 ProjectA0 0 0 57 10 SubProjectA 10 1 1 62 60 SubProjectD 10 4 1 64 63 SubProject_210 4 1 What could be wrong? Best Regards Jakob Vedel Adeltoft, CTO [EMAIL PROTECTED] WebProof - www.webproof.dk Tel +45 46 32 68 68 Fax +45 46 35 94 94 > -Original Message- > From: Uttam [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 15, 2003 7:57 AM > To: Jakob Vedel Adeltoft; [EMAIL PROTECTED] > Subject: RE: JOIN on same table > > > try this: > > SELECT PG1.* > FROM project_group_list AS PG1 LEFT JOIN project_group_list AS PG2 > ON PG1.ref_project_group_id = PG2.project_group_id > WHERE > (PG1.root_project_group_id=10 AND PG1.inherit=1 AND > PG2.inherit=1) > OR (PG1.project_group_id=10) > OR (PG1.ref_project_group_id=10) > ORDER BY PG1.node_depth > > regards, > -Original Message- > From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED] > Sent: Friday, March 14, 2003 16:30 > To: [EMAIL PROTECTED] > Subject: JOIN on same table > > > Hi there, > I have this table: > > project_group_id ref_project_group_idname > root_project_group_id node_depth > inherit > 100 ProjectA0 0 0 > 5710 SubProjectA 10 1 1 > 5957 SubProjectB 10 2 1 > 6059 SubProjectC 10 3 0 > 6260 SubProjectD 10 4 1 > > project_group_id (int) = primary key. > ref_project_group_id (int) = parent project (if root, then 0) > name (char) = name of project > root_project_group_id (int) = project_group_id for root project (if > root, then 0) > node_depth (int) = how far down in the tree this child is (if > root, then > 0) > inherit (int) = determines if this project inherit information from > parent (no=0, yes=1) > > This would look somehting like this in a treeview: > ProjectA (10) <- node_depth = > 0 (root) > \ > SubProjectA (57)<- > node_depth = 1 > \ > SubProjectB (59)<- > node_depth = 2 > \ > SubProjectC (60)<- > node_depth = 3 > \ > SubProjectD (62)<- > node
JOIN on same table
Hi there, I have this table: project_group_idref_project_group_idnameroot_project_group_id node_depth inherit 10 0 ProjectA0 0 0 57 10 SubProjectA 10 1 1 59 57 SubProjectB 10 2 1 60 59 SubProjectC 10 3 0 62 60 SubProjectD 10 4 1 project_group_id (int) = primary key. ref_project_group_id (int) = parent project (if root, then 0) name (char) = name of project root_project_group_id (int) = project_group_id for root project (if root, then 0) node_depth (int) = how far down in the tree this child is (if root, then 0) inherit (int) = determines if this project inherit information from parent (no=0, yes=1) This would look somehting like this in a treeview: ProjectA (10) <- node_depth = 0 (root) \ SubProjectA (57)<- node_depth = 1 \ SubProjectB (59)<- node_depth = 2 \ SubProjectC (60)<- node_depth = 3 \ SubProjectD (62)<- node_depth = 4 I now wan't all child records (inclusive root) for ProjectA that inherit information. This means that I only wan't records with project_group_id = 10, 57, 59, because 60 is NOT inheriting and it's child project (62) is then not able to inherit. I've made the follwoing query: SELECT PGL1.project_group_id, PGL1.ref_project_group_id, PGL1.name, PGL1.permission_inherit, PGL1.node_depth, PGL1.root_project_group_id FROM project_group_list PGL1 LEFT JOIN project_group_list PGL2 ON PGL1.root_project_group_id = PGL2.root_project_group_id WHERE PGL2.root_project_group_id = 10 AND PGL2.permission_inherit = 0 AND PGL1.node_depth < PGL2.node_depth ORDER BY PGL1.node_depth But this only gives me 57 and 59, but not 10 (root). This query also have a problem, if all projects is inheriting and there is no records with inherit=0, then the query returns no results. What can I do to solve this problem? I hope someone can help me... /Jakob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Looping through parent and childs
I'm new to mySQL and would like to know how do a SELECT where I get all child records whose root parent=1 Here's my table (project_group_list): group_idparent_group_id 1 0 2 0 3 1 4 3 5 4 6 4 This is how my output should be: 1 / 3 / 4 / \ 5 6 I was thinking of something like this: SELECT PGL1.project_group_id, PGL1.ref_parent_project_group_id FROM project_group_list PGL1 JOIN project_group_list PGL2 ON PGL1.group_id = PGL2.parent_group_id WHERE PGL1.group_id = 1 The reason for my subject contain "looping" is because I'm also interested in if mySQL have any way of looping SQL queries? Below query is taken from MS-SQL newsgroup: WHILE @@ROWCOUNT>0 BEGIN INSERT INTO [table] SELECT FROM [table] etc. etc. END /Jakob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
