Need help to incorporate UPDATE statement in working SQL

[suprsnipes]

Hi,

 

I need a little bit of help. I'm want to update the data in field type1 based on the following working query and just can't seem to get it right. 

 

SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

Appreciate the help.

[haku]

What help? You didn't ask a question or tell us what you want to do.

[suprsnipes]

Sorry.

 

I want to 'modify' the working SQL SELECT query to an UPDATE query. The table I want to update is named `test2` and the field is named type1, the data to be updated in this case will simply = buy

[haku]

"UPDATE test2 SET type1=buy"

[suprsnipes]

My apologies, I'm sure it's because I'm a slow learner or that I just don't know what I am doing, or both but I've tried the following and there is an error in the SYNTAX.

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 ' at line 1

 

UPDATE test2 SET type1 = 'buy' (SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1) 

 

 

[haku]

Well your syntax is wrong, but it's not really apparent what you are trying to do.

[suprsnipes]

I'm trying to update the type 1 column, currently the working SQL is creating a virtual table from the `test2` table and joining itself WHERE type1 is = 'N' and previous (previous id) column's type 1 is equal to 'a'.

 

As you can tell I'm comparing one entries data to the previous entries data using the id column which is set to auto_increment.

 

SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

What I want to do is when the data meets the above criteria I want the type1 column to be updated in the original table `test2` from 'N' to 'buy'.

 

I can run an UPDATE query on an existing table no problem but I'm uncertain as to how I can do this in conjunction with a JOIN statement like the above where I'm using a Virtual Table.

 

 

[haku]

You will need to select something other than *. If you do that, you can have something like this:

 

UPDATE table1 SET table1.column1 = 'something' WHERE table1.id IN (SELECT id FROM anothertable).

 

The sub query (in parenthesis) returns a list of ids. If the id from the main query is one of the returned IDs, it will update.

 

[suprsnipes]

This UPDATE statement is a real pain in the butt.

 

SELECT t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1
WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

Where the above code works but I can't seem to get the UPDATE statement below too.

 

UPDATE (Select t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1) 
SET t2.type1 = buy 
WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

I have also tried SET test2.type1 = buy. In both instances I'm getting the following error:

 

#1248 - Every derived table must have its own alias

[suprsnipes]

This UPDATE statement is a real pain in the butt.

 

SELECT t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1
WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

Where the above code works and displays the following;

id1 id1

25 26

27 28

58 59

60 61

62 63

64 65

101 102

121 122

142 143

151 152

164 165

196 197

203 204

234 235

236 237

251 252

292 293

297 298

349 350

355 356

366 367

446 447

475 476

579 580

624 625

626 627

674 675

676 677

708 709

789 790

 

I can't seem to get the SYNTAX right for the UPDATE statement below.

 

UPDATE (Select t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1) 
SET t2.type1 = buy 
WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1

 

I have also tried SET test2.type1 = buy. In both instances I'm getting the following error:

 

#1248 - Every derived table must have its own alias

[fenway]

So? give it an alias.

[suprsnipes]

??? I know this is a stupid question but how do I give it an alias?

[Ken2k7]

What is buy? A column name or just a matching string?

 

As for aliases, Google.

[fenway]

As for aliases, Google.

Not helpful whatsoever... in the same number of characters you could have provided a useful response.

 

Just add "AS derviedTable" after the table.

[Ken2k7]

Not helpful whatsoever... in the same number of characters you could have provided a useful response.

That's what you think.