Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Sql Query With Condition If (Update Or Insert)?

sql

  • Please log in to reply
2 replies to this topic

#1 Stasonix

Stasonix

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 115 posts
  • Programming Language:C++, PHP, JavaScript, Delphi/Object Pascal, Pascal
  • Learning:C++, PHP, JavaScript, Delphi/Object Pascal

Posted 09 June 2012 - 09:29 PM

I have 4 cells (`A`,`B`,`C`,`D`), I need to update them if they're empty, but not all at the same moment, for example: if empty `A` than update `A` -> set to 10, if `A` is full, than check the `B` cell, if it's empty, put there 10, also this might be assigned to other last two cells `C`,`D`, if 1 cell is updated it's no need to update other 3 cells, if all cells are full than INSERT INTO `table` (`A`) VALUES(10).

One I did, but it's wrong query, because it's updating all cells:


mysql> UPDATE `mytable` SET `A`=10,`B`=10,`C`=10,`D`=10 WHERE `A` IS NULL OR `B` IS NULL OR `C` IS NULL OR `D` IS NULL LIMIT 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

hope on help.
  • 0

#2 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 10 June 2012 - 07:14 AM

I believe you need multiple sql commands to achieve this. Can not be achived with only a single command, especially because of this:

if 1 cell is updated it's no need to update other 3 cells...


  • 0

#3 grisha

grisha

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 106 posts
  • Location:Poland
  • Programming Language:C, C++, C#, PHP, JavaScript

Posted 10 June 2012 - 07:51 AM

You can do it using the IF() function if it's available in your DB system. For MySQL:


DROP TABLE IF EXISTS stasonix;
CREATE TABLE stasonix (
    col_a VARCHAR(32) NULL,
    col_b VARCHAR(32) NULL,
    col_c VARCHAR(32) NULL,
    col_d VARCHAR(32) NULL
)  ENGINE=InnoDB COLLATE = utf8_general_ci;

INSERT INTO
    stasonix (col_a, col_b, col_c, col_d)
VALUES
    ('a', '', '', ''),
    ('', 'b', '', ''),
    ('', '', 'c', ''),
    ('', '', '', 'd');
   
SELECT * FROM stasonix;

UPDATE stasonix
SET
    col_d = IF (col_b = '', 'x', col_d),
    col_b = IF (col_c = '' && col_b = '', 'y', col_<img src='http://forum.codecall.net/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />;

SELECT * FROM stasonix;

Produces:

+-------+-------+-------+-------+
| col_a | col_b | col_c | col_d |
+-------+-------+-------+-------+
| a	 | y	 |	   | x	 |
|	   | b	 |	   |	   |
|	   |	   | c	 | x	 |
|	   | y	 |	   | x	 |
+-------+-------+-------+-------+

  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download