[MySQL] Do these MSSQL queries exist in MySQL?

[michalchojno]

I worked with MSSQL so far and now I try to catch up on MySQL, which seems a primitively shortened 'brother' of MSSQL.

 

Do the following MSSQL queries have their MySQL counterparts: (or can be somehow replaced and if so, how?)

 

1. In general can I define a multiple-line query with PHP using mysql_query() command? Example:

$q = ("Select distinct amount from table1 where owner = 1 and id =1

Select distinct amount from table2 where owner = 1 and id =1);

$r = mysql_query ($q, $db);

Will that work somehow?

This should generate 2 figures as a result.

 

2. I can't seem to get variables to work. In MSSQL id would go like this:

define @mon int, @yea char(4)

select @mon = 2

select @yea = 'abcd'

 

3. I can't seem to use Update with Join (I use aliases too). Say something like this:

update p

set p.col1 = a.col2

from table1 p join table2 a

on p.owner = a.owner and p.id = a.id

The follwing query seems to work OK, thou:

update table

set p.col1 = 10

where col2 = 1 and col3 = 5

 

4. How deep can I nest queries? (in MSSQL it was 10 times if I remember correctly).

 

[kickstart]

Hi

 

For multiples something like:-

 

$q = ("SELECT * FROM (Select distinct amount from table1 where owner = 1 and id =1) a,(Select distinct amount from table2 where owner = 1 and id =1) b;

 

define @mon int, @yea char(4)

select @mon = 2

select @yea = 'abcd'

 

Not really sure about functions in MySQL.

 

For joins on updates something like this.

 

update table1 p join table2 a

set p.col1 = a.col2

WHERE p.owner = a.owner and p.id = a.id

 

All the best

 

Keith

[michalchojno]

Multiples:

 

Not sure if I get it right. I want to execute a whole SQL code with one PHP statement. Let's say I have test.sql MSSQL file. Can I execute multiple lines of code with one PHP query?

 

Similar to stored procedures in MSSQL. They don't exists in MySQL?

[kickstart]

Hi

 

Thought you meant 2 selects each returning a value on one row.

 

MySQL does support stored procedures:-

 

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

 

All the best

 

Keith

[michalchojno]

Ah! That is excellent! Many thanks for this tip. Stored procedures are such a must use.

 

What about the follwoing. Does MySQL have these too?

1. cursor?

2. "Select Into" query?

3. DTS?

4. Jobs?

 

Still, what about using multiple lines of SQL code with PHP?

 

Let me give you an example of what I mean. I can execute different lines of SQL code one by one:

$q1 = mysql_query ("Select row from table1", $db);

$q2 = mysql_query ("Delete from table1 where row = 1", $db);

$q3 = mysql_query ("Select owner from table1 where id = 1", $db);

Is there a way to do it all in one query? So it's considered as one.

 

 

[kickstart]

Hi

 

Not heard of cursors since using DB2 under PL/1 on a mainframe. Just use recordsets for about the same functionaity.

 

Not sure about SELECT INTO. I know MySQL uses it for stored functions.

 

Not sure about DTS. Quick google and it looks like it can be used, but I have zero idea of how to.

 

Never tried merging queries. Might well be possible (quick try in phpmyadmin and it works, but that might be manually splitting up the queries).

 

All the best

 

Keith