CyberX

mobile

MySQL UPDATE query without WHERE statement simple

There are some interesting things in world of SQL thats you can like to use. For example we can look at the statement thats works like INSERT MySQL query when you define auto increment field value of zero and work like an UPDATE MySQL query when it's defined as existing auto increment id.

Merge insert and update queries
Merge insert and update queries
INSERT INTO `revolver__comments` (`field_id`, `field_content`) 
VALUES ('0', 'TEST LAST INSERT') 
ON DUPLICATE KEY UPDATE `field_id`='0', `field_content`='TEST LAST INSERT';

Look at this statement. If field_id=0 it works like INSERT query but we can use it for UPDATE query also in one instruction:

INSERT INTO `revolver__comments` (`field_id`, `field_content`) 
VALUES ('5', 'TEST UPDATE') 
ON DUPLICATE KEY UPDATE `field_id`='5', `field_content`='TEST UPDATE';

With code bellow MySQL do something like update but it's not need to write WHERE instruction and into field_id having index 5 row fields will putted new data.

Where to use it? For example it can be used when you programming own DataBase engine. For example, my DataBase based on structures instead of queries and I can write something like that:

$STRUCT_COMMENTS = [
	'field_id' => [
		'type'   => 'num', // int
		'auto'   => true,  // auto increment
		'length' => 255,
		'value'  => 0
	],
	'field_node_id' => [
		'type'   => 'num',
		'length' => 50,
		'fill'	 => true
	],
	'field_user_id' => [
		'type'   => 'num',
		'length' => 50,
		'fill'	 => true
	],
	'field_user_name' => [
		'type'   => 'text',
		'length' => 100,
		'fill'	 => true
	],
	'field_content' => [
		'type'   => 'text', // varchar
		'length' => 30000,
		'fill'   => true
	],
	'field_time' => [
		'type'   => 'text',
		'length' => 100,
		'fill'	 => true
	],
	'field_published' => [
		'type' 	 => 'num',
		'length' => 1,
		'fill'	 => false
	]
];

It's an example of table in data base thats created fast and simple with:

// create comments
$dbx::query('c', 'revolver__comments', $STRUCT_COMMENTS);

And next I can insert or update comments data using following structure:

// create comments

$STRUCT = [
	'field_id' => [
		'value'  => 0
	],
	'field_node_id' => [
		'value'	 => 2334
	],
	'field_user_id' => [
		'value'	 => 1
	],
	'field_user_name' => [
		'value'	 => 'CyberX'
	],
	'field_content' => [
		'value'	 => 'Comment text'
	],
	'field_time' => [
		'value'	 => '11/07/2018'
	],
	'field_published' => [
		'value'	 => 1
	]
];

$dbx::query('in', 'revolver__comments', $STRUCT);

Code bellow does an INSERT because field_id value is 0. If this value have an index of existing comment in database this instruction will work like UPDATE. Simple.

Next time I should simplify DELETE query because I do not like WHERE statement.

Reviews

You can write here as guest with moderation. Please confirm your person if you have an account or register.

Add a review as guest
Lets draw: